使用 SQL 命令安装和配置连接器¶

-- Specify values as required by your installation
SET application_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW'; -- use the same name as provided in the installation
SET connector_warehouse = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_WAREHOUSE';
SET servicenow_instance_domain = '<servicenow_instance>.service-now.com';

SET destination_database = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_DEST_DB';
SET destination_schema = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_DEST_SCHEMA';

SET secret_database = 'CONNECTORS_SECRET';
SET secret_schema = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW';
SET secret_database_schema = $secret_database || '.' || $secret_schema;
SET secret_fqn = $secret_database_schema || '.' || 'SECRET';

SET network_rule_fqn = $secret_database_schema || '.' || 'NETWORK_RULE';
SET external_access_integration_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_EXTERNAL_ACCESS_INTEGRATION';

SET destination_database_schema = $destination_database || '.' || $destination_schema;
SET servicenow_instance_url = 'https://' || $servicenow_instance_domain || '/';

-- Create essential objects
USE ROLE accountadmin;

CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($connector_warehouse)
   WAREHOUSE_SIZE = 'MEDIUM'
   AUTO_RESUME = TRUE;
CREATE DATABASE IF NOT EXISTS IDENTIFIER($secret_database);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($secret_database_schema);
CREATE DATABASE IF NOT EXISTS IDENTIFIER($destination_database);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($destination_database_schema);

-- Populate with your credentials
CREATE SECRET IDENTIFIER($secret_fqn)
   TYPE = PASSWORD
   USERNAME = '<servicenow_login>'
   PASSWORD = '<servicenow_password>';

-- None of the following commands should require any changes
CREATE NETWORK RULE IDENTIFIER($network_rule_fqn)
   MODE = 'EGRESS'
   TYPE = 'HOST_PORT'
   VALUE_LIST = ($servicenow_instance_domain);

CREATE PROCEDURE execute_immediate_create_ea_integration()
RETURNS VARIANT
EXECUTE AS caller
AS
BEGIN
   EXECUTE IMMEDIATE '
      CREATE EXTERNAL ACCESS INTEGRATION IDENTIFIER($external_access_integration_name)
      ALLOWED_NETWORK_RULES = ($network_rule_fqn)
      ALLOWED_AUTHENTICATION_SECRETS = ('  ||  $secret_fqn  ||  ') ENABLED = TRUE
   ';
END;
CALL execute_immediate_create_ea_integration();
DROP PROCEDURE IF EXISTS execute_immediate_create_ea_integration();

GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION IDENTIFIER($application_name);
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO APPLICATION IDENTIFIER($application_name);

GRANT USAGE ON WAREHOUSE IDENTIFIER($connector_warehouse) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON DATABASE IDENTIFIER($destination_database) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);

GRANT CREATE TABLE ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);
GRANT CREATE VIEW ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);

GRANT USAGE ON INTEGRATION IDENTIFIER($external_access_integration_name) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON DATABASE IDENTIFIER($secret_database) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON SCHEMA IDENTIFIER($secret_database_schema) TO APPLICATION IDENTIFIER($application_name);
GRANT READ ON SECRET IDENTIFIER($secret_fqn) TO APPLICATION IDENTIFIER($application_name);

CALL SYSTEM$GRANT_OWNERSHIP_TO_APPLICATION($application_name, true, $destination_database, $destination_schema);

USE APPLICATION IDENTIFIER($application_name);

-- Recommended to call one by one as the response might contain an error
CALL CONFIGURE_CONNECTOR({
   'warehouse': $connector_warehouse,
   'destination_database': $destination_database,
   'destination_schema': $destination_schema
});

CALL SET_CONNECTION_CONFIGURATION({
   'service_now_url': $servicenow_instance_url,
   'secret': $secret_fqn,
   'external_access_integration': $external_access_integration_name
});

-- Remove the 'journal_table' parameter if you don't want to track deleted records
CALL FINALIZE_CONNECTOR_CONFIGURATION({
   'journal_table': 'sys_audit_delete'
});

-- Specify values as required by your installation
SET application_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW'; -- use the same name as provided in the installation
SET connector_warehouse = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_WAREHOUSE';
SET servicenow_instance_domain = '<servicenow_instance>.service-now.com';

SET destination_database = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_DEST_DB';
SET destination_schema = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_DEST_SCHEMA';

SET secret_database = 'CONNECTORS_SECRET';
SET secret_schema = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW';
SET secret_database_schema = $secret_database || '.' || $secret_schema;
SET secret_fqn = $secret_database_schema || '.' || 'SECRET';

SET network_rule_fqn = $secret_database_schema || '.' || 'NETWORK_RULE';
SET external_access_integration_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_EXTERNAL_ACCESS_INTEGRATION';
SET security_integration_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_SECURITY_INTEGRATION';

SET destination_database_schema = $destination_database || '.' || $destination_schema;
SET servicenow_instance_url = 'https://' || $servicenow_instance_domain || '/';
SET oauth_token_endpoint = $servicenow_instance_url || 'oauth_token.do';

-- Create essential objects
USE ROlE accountadmin;

CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($connector_warehouse)
   WAREHOUSE_SIZE = 'MEDIUM'
   AUTO_RESUME = TRUE;
CREATE DATABASE IF NOT EXISTS IDENTIFIER($secret_database);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($secret_database_schema);
CREATE DATABASE IF NOT EXISTS IDENTIFIER($destination_database);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($destination_database_schema);

-- Populate with your credentials
CREATE SECURITY INTEGRATION IDENTIFIER($security_integration_name)
   TYPE = API_AUTHENTICATION
   AUTH_TYPE = OAUTH2
   OAUTH_CLIENT_AUTH_METHOD = CLIENT_SECRET_POST
   OAUTH_CLIENT_ID = '<client_id>'
   OAUTH_CLIENT_SECRET = '<client_secret>'
   OAUTH_TOKEN_ENDPOINT = $oauth_token_endpoint
   ENABLED = TRUE;

CREATE SECRET IDENTIFIER($secret_fqn)
   TYPE = OAUTH2
   OAUTH_REFRESH_TOKEN = '<refresh_token>'
   OAUTH_REFRESH_TOKEN_EXPIRY_TIME = '<expiry time>'
   API_AUTHENTICATION = $security_integration_name;

-- None of the following commands should require any changes
CREATE NETWORK RULE IDENTIFIER($network_rule_fqn)
   MODE = 'EGRESS'
   TYPE = 'HOST_PORT'
   VALUE_LIST = ($servicenow_instance_domain);

CREATE PROCEDURE execute_immediate_create_ea_integration()
RETURNS VARIANT
EXECUTE AS caller
AS
BEGIN
   EXECUTE IMMEDIATE '
      CREATE EXTERNAL ACCESS INTEGRATION IDENTIFIER($external_access_integration_name)
      ALLOWED_NETWORK_RULES = ($network_rule_fqn)
      ALLOWED_AUTHENTICATION_SECRETS = ('  ||  $secret_fqn  ||  ') ENABLED = TRUE
   ';
END;
CALL execute_immediate_create_ea_integration();
DROP PROCEDURE IF EXISTS execute_immediate_create_ea_integration();

GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION IDENTIFIER($application_name);
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO APPLICATION IDENTIFIER($application_name);

GRANT USAGE ON WAREHOUSE IDENTIFIER($connector_warehouse) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON DATABASE IDENTIFIER($destination_database) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);

GRANT CREATE TABLE ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);
GRANT CREATE VIEW ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);

GRANT USAGE ON INTEGRATION IDENTIFIER($external_access_integration_name) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON DATABASE IDENTIFIER($secret_database) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON SCHEMA IDENTIFIER($secret_database_schema) TO APPLICATION IDENTIFIER($application_name);
GRANT READ ON SECRET IDENTIFIER($secret_fqn) TO APPLICATION IDENTIFIER($application_name);

CALL SYSTEM$GRANT_OWNERSHIP_TO_APPLICATION($application_name, true, $destination_database, $destination_schema);

USE APPLICATION IDENTIFIER($application_name);

-- Recommended to call one by one as the response might contain an error
CALL CONFIGURE_CONNECTOR({
   'warehouse': $connector_warehouse,
   'destination_database': $destination_database,
   'destination_schema': $destination_schema
});

CALL SET_CONNECTION_CONFIGURATION({
   'service_now_url': $servicenow_instance_url,
   'secret': $secret_fqn,
   'external_access_integration': $external_access_integration_name
});

-- Remove the 'journal_table' parameter if you don't want to track deleted records
CALL FINALIZE_CONNECTOR_CONFIGURATION({
   'journal_table': 'sys_audit_delete'
});

-- Specify values as required by your installation
SET application_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW'; -- use the same name as provided in the installation
SET connector_warehouse = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_WAREHOUSE';
SET servicenow_instance_domain = '<servicenow_instance>.service-now.com';

SET destination_database = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_DEST_DB';
SET destination_schema = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_DEST_SCHEMA';

SET secret_database = 'CONNECTORS_SECRET';
SET secret_schema = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW';
SET secret_database_schema = $secret_database || '.' || $secret_schema;
SET secret_fqn = $secret_database_schema || '.' || 'SECRET';

SET network_rule_fqn = $secret_database_schema || '.' || 'NETWORK_RULE';
SET external_access_integration_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_EXTERNAL_ACCESS_INTEGRATION';
SET security_integration_name = 'SNOWFLAKE_CONNECTOR_FOR_SERVICENOW_SECURITY_INTEGRATION';

SET destination_database_schema = $destination_database || '.' || $destination_schema;
SET servicenow_instance_url = 'https://' || $servicenow_instance_domain || '/';
SET oauth_token_endpoint = $servicenow_instance_url || 'oauth_token.do';

-- Create essential objects
USE ROlE accountadmin;

CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($connector_warehouse)
   WAREHOUSE_SIZE = 'MEDIUM'
   AUTO_RESUME = TRUE;
CREATE DATABASE IF NOT EXISTS IDENTIFIER($secret_database);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($secret_database_schema);
CREATE DATABASE IF NOT EXISTS IDENTIFIER($destination_database);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($destination_database_schema);

-- Populate with your credentials
CREATE SECURITY INTEGRATION IDENTIFIER($security_integration_name)
   TYPE = API_AUTHENTICATION
   AUTH_TYPE = OAUTH2
   OAUTH_CLIENT_AUTH_METHOD = CLIENT_SECRET_POST
   OAUTH_CLIENT_ID = '<client_id>'
   OAUTH_CLIENT_SECRET = '<client_secret>'
   OAUTH_TOKEN_ENDPOINT = $oauth_token_endpoint
   OAUTH_GRANT = 'CLIENT_CREDENTIALS'
   OAUTH_ALLOWED_SCOPES = ('useraccount')
   ENABLED = TRUE;

CREATE SECRET IDENTIFIER($secret_fqn)
   TYPE = OAUTH2
   API_AUTHENTICATION = $security_integration_name
   OAUTH_SCOPES=('useraccount');

-- None of the following commands should require any changes
CREATE NETWORK RULE IDENTIFIER($network_rule_fqn)
   MODE = 'EGRESS'
   TYPE = 'HOST_PORT'
   VALUE_LIST = ($servicenow_instance_domain);

CREATE PROCEDURE execute_immediate_create_ea_integration()
RETURNS VARIANT
EXECUTE AS caller
AS
BEGIN
   EXECUTE IMMEDIATE '
      CREATE EXTERNAL ACCESS INTEGRATION IDENTIFIER($external_access_integration_name)
      ALLOWED_NETWORK_RULES = ($network_rule_fqn)
      ALLOWED_AUTHENTICATION_SECRETS = ('  ||  $secret_fqn  ||  ') ENABLED = TRUE
   ';
END;
CALL execute_immediate_create_ea_integration();
DROP PROCEDURE IF EXISTS execute_immediate_create_ea_integration();

GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION IDENTIFIER($application_name);
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO APPLICATION IDENTIFIER($application_name);

GRANT USAGE ON WAREHOUSE IDENTIFIER($connector_warehouse) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON DATABASE IDENTIFIER($destination_database) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);

GRANT CREATE TABLE ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);
GRANT CREATE VIEW ON SCHEMA IDENTIFIER($destination_database_schema) TO APPLICATION IDENTIFIER($application_name);

GRANT USAGE ON INTEGRATION IDENTIFIER($external_access_integration_name) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON DATABASE IDENTIFIER($secret_database) TO APPLICATION IDENTIFIER($application_name);
GRANT USAGE ON SCHEMA IDENTIFIER($secret_database_schema) TO APPLICATION IDENTIFIER($application_name);
GRANT READ ON SECRET IDENTIFIER($secret_fqn) TO APPLICATION IDENTIFIER($application_name);

CALL SYSTEM$GRANT_OWNERSHIP_TO_APPLICATION($application_name, true, $destination_database, $destination_schema);

USE APPLICATION IDENTIFIER($application_name);

-- Recommended to call one by one as the response might contain an error
CALL CONFIGURE_CONNECTOR({
   'warehouse': $connector_warehouse,
   'destination_database': $destination_database,
   'destination_schema': $destination_schema
});

CALL SET_CONNECTION_CONFIGURATION({
   'service_now_url': $servicenow_instance_url,
   'secret': $secret_fqn,
   'external_access_integration': $external_access_integration_name
});

-- Remove the 'journal_table' parameter if you don't want to track deleted records
CALL FINALIZE_CONNECTOR_CONFIGURATION({
   'journal_table': 'sys_audit_delete'
});