外表创建 本章节主要介绍最佳实践附录:外表创建。 本小节所附外表语法用于获取本Tutorial使用到的示例数据。这些示例数据存储在OBS存储桶中,该存储桶向所有经过身份验证的云用户提供了读取权限。 说明 l 注意,以下语句中的代表OBS桶名,仅支持部分区域。DWS集群不支持跨区域访问OBS桶数据。 l 运行时请将示例中的ACCESSKEY和SECRETACCESSKEY替换用户帐户自己的凭证。 CREATE FOREIGN TABLE obsfromstoresales001 ( sssolddatesk integer , sssoldtimesk integer , ssitemsk integer not null, sscustomersk integer , sscdemosk integer , sshdemosk integer , ssaddrsk integer , ssstoresk integer , sspromosk integer , ssticketnumber bigint not null, ssquantity integer , sswholesalecost decimal(7,2) , sslistprice decimal(7,2) , sssalesprice decimal(7,2) , ssextdiscountamt decimal(7,2) , ssextsalesprice decimal(7,2) , ssextwholesalecost decimal(7,2) , ssextlistprice decimal(7,2) , ssexttax decimal(7,2) , sscouponamt decimal(7,2) , ssnetpaid decimal(7,2) , ssnetpaidinctax decimal(7,2) , ssnetprofit decimal(7,2) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/storesales', FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromstoresales001; CREATE FOREIGN TABLE obsfromdatedim001 ( ddatesk integer not null, ddateid char(16) not null, ddate date , dmonthseq integer , dweekseq integer , dquarterseq integer , dyear integer , ddow integer , dmoy integer , ddom integer , dqoy integer , dfyyear integer , dfyquarterseq integer , dfyweekseq integer , ddayname char(9) , dquartername char(6) , dholiday char(1) , dweekend char(1) , dfollowingholiday char(1) , dfirstdom integer , dlastdom integer , dsamedayly integer , dsamedaylq integer , dcurrentday char(1) , dcurrentweek char(1) , dcurrentmonth char(1) , dcurrentquarter char(1) , dcurrentyear char(1) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/datedim' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromdatedim001; CREATE FOREIGN TABLE obsfromstore001 ( sstoresk integer not null, sstoreid char(16) not null, srecstartdate date , srecenddate date , scloseddatesk integer , sstorename varchar(50) , snumberemployees integer , sfloorspace integer , shours char(20) , smanager varchar(40) , smarketid integer , sgeographyclass varchar(100) , smarketdesc varchar(100) , smarketmanager varchar(40) , sdivisionid integer , sdivisionname varchar(50) , scompanyid integer , scompanyname varchar(50) , sstreetnumber varchar(10) , sstreetname varchar(60) , sstreettype char(15) , ssuitenumber char(10) , scity varchar(60) , scounty varchar(30) , sstate char(2) , szip char(10) , scountry varchar(20) , sgmtoffset decimal(5,2) , staxprecentage decimal(5,2) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/store' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromstore001; CREATE FOREIGN TABLE obsfromitem001 ( iitemsk integer not null, iitemid char(16) not null, irecstartdate date , irecenddate date , iitemdesc varchar(200) , icurrentprice decimal(7,2) , iwholesalecost decimal(7,2) , ibrandid integer , ibrand char(50) , iclassid integer , iclass char(50) , icategoryid integer , icategory char(50) , imanufactid integer , imanufact char(50) , isize char(20) , iformulation char(20) , icolor char(20) , iunits char(10) , icontainer char(10) , imanagerid integer , iproductname char(50) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/item' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromitem001; CREATE FOREIGN TABLE obsfromtimedim001 ( ttimesk integer not null, ttimeid char(16) not null, ttime integer , thour integer , tminute integer , tsecond integer , tampm char(2) , tshift char(20) , tsubshift char(20) , tmealtime char(20) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/timedim' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromtimedim001; CREATE FOREIGN TABLE obsfrompromotion001 ( ppromosk integer not null, ppromoid char(16) not null, pstartdatesk integer , penddatesk integer , pitemsk integer , pcost decimal(15,2) , presponsetarget integer , ppromoname char(50) , pchanneldmail char(1) , pchannelemail char(1) , pchannelcatalog char(1) , pchanneltv char(1) , pchannelradio char(1) , pchannelpress char(1) , pchannelevent char(1) , pchanneldemo char(1) , pchanneldetails varchar(100) , ppurpose char(15) , pdiscountactive char(1) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/promotion' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfrompromotion001; CREATE FOREIGN TABLE obsfromcustomerdemographics001 ( cddemosk integer not null, cdgender char(1) , cdmaritalstatus char(1) , cdeducationstatus char(20) , cdpurchaseestimate integer , cdcreditrating char(10) , cddepcount integer , cddepemployedcount integer , cddepcollegecount integer ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/customerdemographics' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromcustomerdemographics001; CREATE FOREIGN TABLE obsfromcustomeraddress001 ( caaddresssk integer not null, caaddressid char(16) not null, castreetnumber char(10) , castreetname varchar(60) , castreettype char(15) , casuitenumber char(10) , cacity varchar(60) , cacounty varchar(30) , castate char(2) , cazip char(10) , cacountry varchar(20) , cagmtoffset float4 , calocationtype char(20) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/customeraddress' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromcustomeraddress001; CREATE FOREIGN TABLE obsfromhouseholddemographics001 ( hddemosk integer not null, hdincomebandsk integer , hdbuypotential char(15) , hddepcount integer , hdvehiclecount integer ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/householddemographics' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromhouseholddemographics001; CREATE FOREIGN TABLE obsfromcustomer001 ( ccustomersk integer not null, ccustomerid char(16) not null, ccurrentcdemosk integer , ccurrenthdemosk integer , ccurrentaddrsk integer , cfirstshiptodatesk integer , cfirstsalesdatesk integer , csalutation char(10) , cfirstname char(20) , clastname char(30) , cpreferredcustflag char(1) , cbirthday integer , cbirthmonth integer , cbirthyear integer , cbirthcountry varchar(20) , clogin char(13) , cemailaddress char(50) , clastreviewdate char(10) ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/customer' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromcustomer001; CREATE FOREIGN TABLE obsfromincomeband001 ( ibincomebandsk integer not null, iblowerbound integer , ibupperbound integer ) SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/incomeband' , FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) WITH errobsfromincomeband001;
来自: