文档首页 > > 最佳实践> 教程:调优表设计> 附录:表创建语法>

外表创建

外表创建

分享
更新时间:2021/02/18 GMT+08:00

本小节所附外表语法用于获取本Tutorial使用到的示例数据。这些示例数据存储在OBS存储桶中,该存储桶向所有经过身份验证的云用户提供了读取权限。运行时请将示例中的ACCESS_KEY和SECRET_ACCESS_KEY替换用户帐户自己的凭证。

CREATE FOREIGN TABLE obs_from_store_sales_001 ( ss_sold_date_sk integer , ss_sold_time_sk integer , ss_item_sk integer not null, ss_customer_sk integer , ss_cdemo_sk integer , ss_hdemo_sk integer , ss_addr_sk integer , ss_store_sk integer , ss_promo_sk integer , ss_ticket_number bigint not null, ss_quantity integer , ss_wholesale_cost decimal(7,2) , ss_list_price decimal(7,2) , ss_sales_price decimal(7,2) , ss_ext_discount_amt decimal(7,2) , ss_ext_sales_price decimal(7,2) , ss_ext_wholesale_cost decimal(7,2) , ss_ext_list_price decimal(7,2) , ss_ext_tax decimal(7,2) , ss_coupon_amt decimal(7,2) , ss_net_paid decimal(7,2) , ss_net_paid_inc_tax decimal(7,2) , ss_net_profit decimal(7,2) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comstore_sales/store_sales' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_store_sales_001; CREATE FOREIGN TABLE obs_from_date_dim_001 ( d_date_sk integer not null, d_date_id char(16) not null, d_date date , d_month_seq integer , d_week_seq integer , d_quarter_seq integer , d_year integer , d_dow integer , d_moy integer , d_dom integer , d_qoy integer , d_fy_year integer , d_fy_quarter_seq integer , d_fy_week_seq integer , d_day_name char(9) , d_quarter_name char(6) , d_holiday char(1) , d_weekend char(1) , d_following_holiday char(1) , d_first_dom integer , d_last_dom integer , d_same_day_ly integer , d_same_day_lq integer , d_current_day char(1) , d_current_week char(1) , d_current_month char(1) , d_current_quarter char(1) , d_current_year char(1) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comdate_dim/date_dim' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_date_dim_001; CREATE FOREIGN TABLE obs_from_store_001 ( s_store_sk integer not null, s_store_id char(16) not null, s_rec_start_date date , s_rec_end_date date , s_closed_date_sk integer , s_store_name varchar(50) , s_number_employees integer , s_floor_space integer , s_hours char(20) , s_manager varchar(40) , s_market_id integer , s_geography_class varchar(100) , s_market_desc varchar(100) , s_market_manager varchar(40) , s_division_id integer , s_division_name varchar(50) , s_company_id integer , s_company_name varchar(50) , s_street_number varchar(10) , s_street_name varchar(60) , s_street_type char(15) , s_suite_number char(10) , s_city varchar(60) , s_county varchar(30) , s_state char(2) , s_zip char(10) , s_country varchar(20) , s_gmt_offset decimal(5,2) , s_tax_precentage decimal(5,2) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comstore/store' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_store_001; CREATE FOREIGN TABLE obs_from_item_001 ( i_item_sk integer not null, i_item_id char(16) not null, i_rec_start_date date , i_rec_end_date date , i_item_desc varchar(200) , i_current_price decimal(7,2) , i_wholesale_cost decimal(7,2) , i_brand_id integer , i_brand char(50) , i_class_id integer , i_class char(50) , i_category_id integer , i_category char(50) , i_manufact_id integer , i_manufact char(50) , i_size char(20) , i_formulation char(20) , i_color char(20) , i_units char(10) , i_container char(10) , i_manager_id integer , i_product_name char(50) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comitem/item' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_item_001; CREATE FOREIGN TABLE obs_from_time_dim_001 ( t_time_sk integer not null, t_time_id char(16) not null, t_time integer , t_hour integer , t_minute integer , t_second integer , t_am_pm char(2) , t_shift char(20) , t_sub_shift char(20) , t_meal_time char(20) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comtime_dim/time_dim' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_time_dim_001; CREATE FOREIGN TABLE obs_from_promotion_001 ( p_promo_sk integer not null, p_promo_id char(16) not null, p_start_date_sk integer , p_end_date_sk integer , p_item_sk integer , p_cost decimal(15,2) , p_response_target integer , p_promo_name char(50) , p_channel_dmail char(1) , p_channel_email char(1) , p_channel_catalog char(1) , p_channel_tv char(1) , p_channel_radio char(1) , p_channel_press char(1) , p_channel_event char(1) , p_channel_demo char(1) , p_channel_details varchar(100) , p_purpose char(15) , p_discount_active char(1) ) SERVER gsmpp_server OPTIONS ( LOCATIONobs.cn-north-1.myhuaweicloud.compromotion/promotion' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_promotion_001; CREATE FOREIGN TABLE obs_from_customer_demographics_001 ( cd_demo_sk integer not null, cd_gender char(1) , cd_marital_status char(1) , cd_education_status char(20) , cd_purchase_estimate integer , cd_credit_rating char(10) , cd_dep_count integer , cd_dep_employed_count integer , cd_dep_college_count integer ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comcustomer_demographics/customer_demographics' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_customer_demographics_001; CREATE FOREIGN TABLE obs_from_customer_address_001 ( ca_address_sk integer not null, ca_address_id char(16) not null, ca_street_number char(10) , ca_street_name varchar(60) , ca_street_type char(15) , ca_suite_number char(10) , ca_city varchar(60) , ca_county varchar(30) , ca_state char(2) , ca_zip char(10) , ca_country varchar(20) , ca_gmt_offset float4 , ca_location_type char(20) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comcustomer_address/customer_address' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_customer_address_001; CREATE FOREIGN TABLE obs_from_household_demographics_001 ( hd_demo_sk integer not null, hd_income_band_sk integer , hd_buy_potential char(15) , hd_dep_count integer , hd_vehicle_count integer ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comhousehold_demographics/household_demographics' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_household_demographics_001; CREATE FOREIGN TABLE obs_from_customer_001 ( c_customer_sk integer not null, c_customer_id char(16) not null, c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comcustomer/customer' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_customer_001; CREATE FOREIGN TABLE obs_from_income_band_001 ( ib_income_band_sk integer not null, ib_lower_bound integer , ib_upper_bound integer ) SERVER gsmpp_server OPTIONS ( LOCATION obs.cn-north-1.myhuaweicloud.comincome_band/income_band' , FORMAT 'text', DELIMITER '|', ENCODING 'utf8', NOESCAPING 'true', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', REJECT_LIMIT 'unlimited', CHUNKSIZE '64' ) WITH err_obs_from_income_band_001;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!非常感谢您的反馈,我们会继续努力做到更好!
反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问