ACCESSDATA 命令

您需要从 Amazon Redshift 云数据服务导入数据。为此，您使用 Analytics Amazon Redshift 数据连接器：

ACCESSDATA64 CONNECTOR NAME "Amazon Redshift" USER "ACL_user" PROMPT_PASSWORD TO "Entitlement_History.FIL" CHARMAX 50 MEMOMAX 100 SOURCE( boolsaschar=0;cachesize=100;database=usage;declarefetchmode=0;maxbytea=255;maxlongvarchar=8190;maxvarchar=255;port=5439;servername=acl_test.highbond.com;singlerowmode=1;sslmode=require;textaslongvarchar=0;usemultiplestatments=0;useunicode=1)
  SQL_QUERY(
    SELECT
      "entitlement_history"."organization" AS "organization",
      "entitlement_history"."user_email" AS "user_email",
      "entitlement_history"."plan_id" AS "plan_id",
      "entitlement_history"."date_from" AS "date_from",
      "entitlement_history"."date_to" AS "date_to"
    FROM
      "prm"."entitlement_history" "entitlement_history"
  ) END_QUERY

您需要从 Microsoft Access 数据库导入数据。为此，您使用 Windows ODBC 驱动程序连接到 MS Access 并完成导入：

ACCESSDATA32 ODBC "Driver" NAME "Microsoft Access Driver (*.mdb)" TO "Invoices.FIL" CHARMAX 50 MEMOMAX 100 SOURCE( dbq=C:\Users\lachlan_murray\Documents\ACL Data\Sample Data Files\Sample.mdb;defaultdir=C:\Users\lachlan_murray\Documents\ACL Data\Sample Data Files;driverid=281;fil=MS Access;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;threads=3;usercommitsync=Yes)
  SQL_QUERY(
    SELECT
      `Customer`.`CustID` AS `CustID`,
      `Customer`.`Company` AS `Company`,
      `Customer`.`Address` AS `Address`,
      `Customer`.`City` AS `City`,
      `Customer`.`Region` AS `Region`,
      `Customer`.`PostalCode` AS `PostalCode`,
      `Customer`.`Country` AS `Country`,
      `Customer`.`Phone` AS `Phone`,
      `Orders`.`OrderID` AS `OrderID`,
      `Orders`.`CustID` AS `Orders_CustID`,
      `Orders`.`ProdID` AS `ProdID`,
      `Orders`.`OrderDate` AS `OrderDate`,
      `Orders`.`Quantity` AS `Quantity`,
      `Product`.`ProdID` AS `Product_ProdID`,
      `Product`.`ProdName` AS `ProdName`,
      `Product`.`UnitPrice` AS `UnitPrice`,
      `Product`.`Descript` AS `Descript`,
      `Product`.`ShipWt` AS `ShipWt`
    FROM
      (`Customer` `Customer`
    INNER JOIN
      `Orders` `Orders`
      ON `Customer`.`CustID` = `Orders`.`CustID`
      )
    INNER JOIN
      `Product` `Product`
      ON `Orders`.`ProdID` = `Product`.`ProdID`
    WHERE
    (
      `Customer`.`Region` = 'BC'
      OR `Customer`.`Region` = 'WA'
    )
  ) END_QUERY

您需要从 Microsoft Excel 文件导入数据。为此，您使用 Windows DSN 连接到 Excel 并完成导入：

ACCESSDATA32 ODBC "Dsn" NAME "Excel Files" TO "Trans_April_15_cutoff.FIL" CHARMAX 50 MEMOMAX 100 SOURCE( dbq=C:\Users\lachlan_murray\Documents\ACL Data\Sample Data Files\Trans_April.xls;defaultdir=C:\Users\lachlan_murray\Documents\ACL Data\Sample Data Files;driverid=1046;maxbuffersize=2048;pagetimeout=5)
  SQL_QUERY(
    SELECT
      `Trans_Apr_`.`CARDNUM` AS `CARDNUM`,
      `Trans_Apr_`.`AMOUNT` AS `AMOUNT`,
      `Trans_Apr_`.`TRANS_DATE` AS `TRANS_DATE`,
      `Trans_Apr_`.`CODES` AS `CODES`,
      `Trans_Apr_`.`CUSTNO` AS `CUSTNO`,
      `Trans_Apr_`.`DESCRIPTION` AS `DESCRIPTION`
    FROM
      `Trans_Apr$` `Trans_Apr_`
    WHERE
      (
          `Trans_Apr_`.`TRANS_DATE` <= {ts '2003-04-15 00:00:00'}
      )
  ) END_QUERY

如果数据源只需要单个密码，请使用 PASSWORD 数字参数。

在下面的例子中：

1. PASSWORD 1 命令提示用户输入一个密码，并且创建一个安全地存储输入的密码的密码定义。
2. 在 ACCESSDATA 命令中，PASSWORD 1 参数引用该密码定义，并且将存储的密码值安全地传入 SOURCE 所指定的连接设置 (auth_accesstoken=[$pwd])。

PASSWORD 1
ACCESSDATA64 CONNECTOR NAME "Concur" PASSWORD 1 TO "Concur_data_import.FIL" CHARMAX 50 MEMOMAX 100 SOURCE( auth_accesstoken=[$pwd];auth_type=OAuth 2.0;enabledoublebuffer=1;host=www.concursolutions.com;useencryptedendpoints=1;userparam=all)
  SQL_QUERY( 
    SELECT
      "List_Items"."Level_7_Code" AS "Level_7_Code",
      "List_Items"."Name" AS "Name",
      "List_Items"."Level_10_Code" AS "Level_10_Code",
      "List_Items"."Level_8_Code" AS "Level_8_Code",
      "List_Items"."URI" AS "URI",
      "List_Items"."Id" AS "Id",
      "List_Items"."Level_3_Code" AS "Level_3_Code",
      "List_Items"."List_Id" AS "List_Id",
      "List_Items"."Level_4_Code" AS "Level_4_Code",
      "List_Items"."Level_1_Code" AS "Level_1_Code",
      "List_Items"."Parent_Id" AS "Parent_Id",
      "List_Items"."Level_2_Code" AS "Level_2_Code",
      "List_Items"."Level_5_Code" AS "Level_5_Code",
      "List_Items"."Level_6_Code" AS "Level_6_Code",
      "List_Items"."Level_9_Code" AS "Level_9_Code" 
    FROM
      "Concur"."List_Items" "List_Items"
  ) END_QUERY

如果数据源需要多个密码或者认证值，请使用 PASSWORD 数字 AS 密码键名称参数。

在下面的例子中：

1. 在 机器人 中，分析标头中的 //PASSWORD 标记会创建四个密码参数，用户必须为这些参数输入验证值。这四个参数创建四个密码定义，以安全地存储输入的值。
2. 在 ACCESSDATA 命令中，这四个 PASSWORD 参数引用密码定义，并且将存储的认证值安全地传入 SOURCE 所指定的连接设置：
   • oauthclientid=
   • oauthclientsecret=
   • oauthaccesstoken=
   • oauthaccesstokensecret=

有关更多信息，请参阅配置 ACCESSDATA 以使用多个密码定义。

COMMENT
//ANALYTIC TYPE IMPORT 导入 Twitter 数据
//PASSWORD 1 输入 OAuth 客户端 ID：
//PASSWORD 2 输入 OAuth 客户端密钥：
//PASSWORD 3 输入 OAuth 访问令牌：
//PASSWORD 4 输入 OAuth 访问令牌密钥：
//RESULT TABLE Twitter_user_data
END

ACCESSDATA64 CONNECTOR NAME "Twitter" PASSWORD 1 AS oauthclientid PASSWORD 2 AS oauthclientsecret PASSWORD 3 AS oauthaccesstoken PASSWORD 4 AS oauthaccesstokensecret TO "Twitter_user_data.FIL" CHARMAX 50 MEMOMAX 100 SOURCE( oauthclientid=;oauthclientsecret=;oauthaccesstoken=;oauthaccesstokensecret=;readonly=true;drivertype=ACL Connector for Twitter;connectonopen=true;convertdatetimetogmt=true;limitkeysize=255;maptolongvarchar=-1;maptowvarchar=true;uppercaseidentifiers=false;supportenhancedsql=true;proxyauthscheme=BASIC;proxyautodetect=true;_persist_=encrypted-dp{AQA ... kX3E8yyh05HoG1rH4bm1lhwudUQ==}})
  SQL_QUERY( 
    SELECT
      "Users"."ID" AS "ID",
      "Users"."Name" AS "Name",
      "Users"."Screen_Name" AS "Screen_Name",
      "Users"."Location" AS "Location",
      "Users"."Profile_URL" AS "Profile_URL",
      "Users"."Lang" AS "Lang",
      "Users"."Created_At" AS "Created_At",
      "Users"."Friends_Count" AS "Friends_Count",
      "Users"."Followers_Count" AS "Followers_Count",
      "Users"."Favourites_Count" AS "Favourites_Count",
      "Users"."Statuses_Count" AS "Statuses_Count",
      "Users"."Time_Zone" AS "Time_Zone",
      "Users"."Following" AS "Following",
      "Users"."Contributors_Enabled" AS "Contributors_Enabled",
      "Users"."Follow_Request_Sent" AS "Follow_Request_Sent",
      "Users"."Listed_Count" AS "Listed_Count",
      "Users"."Description" AS "Description",
      "Users"."Default_Profile" AS "Default_Profile"
    FROM
      "Twitter"."Users" "Users"
  ) END_QUERY

您需要从三个 SAP 表中下载并导入数据，作为后续分析的基础：

• T001
• T002
• T003

最高效的方法就是并行下载这些表。组合一组 ACCESSDATA 命令，一个命令对应一个表。每个命令都使用 ASYNC 参数，指定三个并行执行的下载操作（即，异步执行）。

在执行 ACCESSDATA 命令块之后，您可以使用 GETSAPDATA 命令将下载的表导入 Analytics 项目。

COMMENT 并行下载三个 SAP 表

ACCESSDATA64 CONNECTOR NAME "SAP" USER "irosario" PASSWORD 1 TO "T001.FIL" CHARMAX 50 MEMOMAX 100 ASYNC SOURCE(server=192.0.2.255;client=800;language=EN;instancenumber=01;variablestringlength=256;TemporaryWorkingDirectory=Default)
  SQL_QUERY(
    SELECT * FROM T001
  ) END_QUERY

ACCESSDATA64 CONNECTOR NAME "SAP" USER "irosario" PASSWORD 1 TO "T002.FIL" CHARMAX 50 MEMOMAX 100 ASYNC SOURCE(server=192.0.2.255;client=800;language=EN;instancenumber=01;variablestringlength=256;TemporaryWorkingDirectory=Default)
  SQL_QUERY(
    SELECT * FROM T002
  ) END_QUERY

ACCESSDATA64 CONNECTOR NAME "SAP" USER "irosario" PASSWORD 1 TO "T003.FIL" CHARMAX 50 MEMOMAX 100 ASYNC SOURCE(server=192.0.2.255;client=800;language=EN;instancenumber=01;variablestringlength=256;TemporaryWorkingDirectory=Default)
  SQL_QUERY(
    SELECT * FROM T003
  ) END_QUERY

COMMENT 将已下载的三个表导入 Analytics 项目

GETSAPDATA