您好,登錄后才能下訂單哦!
如何使用Data Lake Analytics + OSS分析CSV格式的TPC-H數(shù)據(jù)集,很多新手對(duì)此不是很清楚,為了幫助大家解決這個(gè)難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來學(xué)習(xí)下,希望你能有所收獲。
如果您已經(jīng)開通,可以跳過該步驟。如果沒有開通,可以參考:https://help.aliyun.com/document_detail/70386.html
進(jìn)行產(chǎn)品開通服務(wù)申請(qǐng)。
可以從這下載TPC-H 100MB的數(shù)據(jù)集:
https://public-datasets-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/tpch_100m_data.zip
登錄阿里云官網(wǎng)的OSS控制臺(tái):https://oss.console.aliyun.com/overview
規(guī)劃您要使用的OSS bucket,創(chuàng)建或選擇好后,點(diǎn)擊“文件管理”,因?yàn)橛?個(gè)數(shù)據(jù)文件,為每個(gè)數(shù)據(jù)文件創(chuàng)建對(duì)應(yīng)的文件目錄:
創(chuàng)建好8個(gè)目錄如下:
點(diǎn)擊進(jìn)入目錄,上傳相應(yīng)的數(shù)據(jù)文件,例如,customer目錄,則上傳customer.tbl文件。
上傳好后,如下圖。然后,依次把其他7個(gè)數(shù)據(jù)文件也上傳到對(duì)應(yīng)的目錄下。
至此,8個(gè)數(shù)據(jù)文件都上傳到了您的OSS bucket中:
oss://xxx/tpch_100m/customer/customer.tbloss://xxx/tpch_100m/lineitem/lineitem.tbloss://xxx/tpch_100m/nation/nation.tbloss://xxx/tpch_100m/orders/orders.tbloss://xxx/tpch_100m/part/part.tbloss://xxx/tpch_100m/partsupp/partsupp.tbloss://xxx/tpch_100m/region/region.tbloss://xxx/tpch_100m/supplier/supplier.tbl
https://openanalytics.console.aliyun.com/
點(diǎn)擊“登錄數(shù)據(jù)庫(kù)”,輸入開通服務(wù)時(shí)分配的用戶名和密碼,登錄Data Lake Analytics控制臺(tái)。
輸入創(chuàng)建SCHEMA的語(yǔ)句,點(diǎn)擊“同步執(zhí)行”。
CREATE SCHEMA tpch_100m with DBPROPERTIES( LOCATION = 'oss://test-bucket-julian-1/tpch_100m/', catalog='oss');
(注意:目前在同一個(gè)阿里云region,Data Lake Analytics的schema名全局唯一,建議schema名盡量根據(jù)業(yè)務(wù)定義,已有重名schema,在創(chuàng)建時(shí)會(huì)提示報(bào)錯(cuò),則請(qǐng)換一個(gè)schema名字。)
Schema創(chuàng)建好后,在“數(shù)據(jù)庫(kù)”的下拉框中,選擇剛剛創(chuàng)建的schema。然后在SQL文本框中輸入建表語(yǔ)句,點(diǎn)擊同步執(zhí)行。
建表語(yǔ)句語(yǔ)法參考:https://help.aliyun.com/document_detail/72006.html
TPC-H對(duì)應(yīng)的8個(gè)表的建表語(yǔ)句如下,分別貼入文檔框中執(zhí)行(LOCATION子句中的數(shù)據(jù)文件位置請(qǐng)根據(jù)您的實(shí)際OSS bucket目錄相應(yīng)修改)。(注意:目前控制臺(tái)中還不支持多個(gè)SQL語(yǔ)句執(zhí)行,請(qǐng)單條語(yǔ)句執(zhí)行。)
CREATE EXTERNAL TABLE nation ( N_NATIONKEY INT, N_NAME STRING, N_ID STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/nation';CREATE EXTERNAL TABLE lineitem ( L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/lineitem';CREATE EXTERNAL TABLE orders ( O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE DATE, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/orders';CREATE EXTERNAL TABLE supplier ( S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/supplier';CREATE EXTERNAL TABLE partsupp ( PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/partsupp';CREATE EXTERNAL TABLE customer ( C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/customer';CREATE EXTERNAL TABLE part ( P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/part';CREATE EXTERNAL TABLE region ( R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'oss://test-bucket-julian-1/tpch_100m/region';
查看更多
建表完畢后,刷新頁(yè)面,在左邊導(dǎo)航條中能看到schema下的8張表。
TPC-H總共22條查詢,如下:
Q1:
SELECT l_returnflag, l_linestatus, Sum(l_quantity) AS sum_qty, Sum(l_extendedprice) AS sum_base_price, Sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, Sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, Avg(l_quantity) AS avg_qty, Avg(l_extendedprice) AS avg_price, Avg(l_discount) AS avg_disc, Count(*) AS count_orderFROM lineitemWHERE l_shipdate <= date '1998-12-01' - INTERVAL '93' dayGROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatusLIMIT 1;
查看更多
Q2:
SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_commentFROM part, supplier, partsupp, nation, regionWHERE p_partkey = ps_partkeyAND s_suppkey = ps_suppkeyAND p_size = 35AND p_type LIKE '%NICKEL'AND s_nationkey = n_nationkeyAND n_regionkey = r_regionkeyAND r_name = 'MIDDLE EAST'
查看更多
Q3:
SELECT l_orderkey, Sum(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriorityFROM customer, orders, lineitemWHERE c_mktsegment = 'AUTOMOBILE'AND c_custkey = o_custkeyAND l_orderkey = o_orderkeyAND o_orderdate < date '1995-03-31'AND l_shipdate > date '1995-03-31'GROUP BY l_orderkey, o_orderdate, o_shippriorityORDER BY revenue DESC, o_orderdateLIMIT 10;
Q4:
SELECT o_orderpriority, Count(*) AS order_countFROM orders, lineitemWHERE o_orderdate >= date '1997-10-01'AND o_orderdate < date '1997-10-01' + INTERVAL '3' monthAND l_orderkey = o_orderkeyAND l_commitdate < l_receiptdateGROUP BY o_orderpriorityORDER BY o_orderpriorityLIMIT 1;
Q5:
SELECT n_name, Sum(l_extendedprice * (1 - l_discount)) AS revenueFROM customer, orders, lineitem, supplier, nation, regionWHERE c_custkey = o_custkeyAND l_orderkey = o_orderkeyAND l_suppkey = s_suppkeyAND c_nationkey = s_nationkeyAND s_nationkey = n_nationkeyAND n_regionkey = r_regionkeyAND r_name = 'ASIA'AND o_orderdate >= date '1995-01-01'AND o_orderdate < date '1995-01-01' + INTERVAL '1' yearGROUP BY n_nameORDER BY revenue DESCLIMIT 1;
Q6:
SELECT sum(l_extendedprice * l_discount) AS revenueFROM lineitemWHERE l_shipdate >= date '1995-01-01'AND l_shipdate < date '1995-01-01' + interval '1' yearAND l_discount between 0.04 - 0.01 AND 0.04 + 0.01AND l_quantity < 24LIMIT 1;
Q7:
SELECT supp_nation, cust_nation, l_year, Sum(volume) AS revenueFROM ( SELECT n1.n_name AS supp_nation, n2.n_name AS cust_nation, Extract(year FROM l_shipdate) AS l_year, l_extendedprice * (1 - l_discount) AS volume FROM supplier, lineitem, orders, customer, nation n1, nation n2 WHERE s_suppkey = l_suppkey AND o_orderkey = l_orderkey AND c_custkey = o_custkey AND s_nationkey = n1.n_nationkey AND c_nationkey = n2.n_nationkey AND ( ( n1.n_name = 'GERMANY' AND n2.n_name = 'INDIA') OR ( n1.n_name = 'INDIA' AND n2.n_name = 'GERMANY') ) AND l_shipdate BETWEEN date '1995-01-01' AND date '1996-12-31' ) AS shippingGROUP BY supp_nation, cust_nation, l_yearORDER BY supp_nation, cust_nation, l_yearLIMIT 1;
查看更多
Q8:
SELECT o_year, Sum( CASE WHEN nation = 'INDIA' THEN volume ELSE 0 end) / Sum(volume) AS mkt_shareFROM ( SELECT Extract(year FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part, supplier, lineitem, orders, customer, nation n1, nation n2, region WHERE p_partkey = l_partkey AND s_suppkey = l_suppkey AND l_orderkey = o_orderkey AND o_custkey = c_custkey AND c_nationkey = n1.n_nationkey AND n1.n_regionkey = r_regionkey AND r_name = 'ASIA' AND s_nationkey = n2.n_nationkey AND o_orderdate BETWEEN date '1995-01-01' AND date '1996-12-31' AND p_type = 'STANDARD ANODIZED STEEL' ) AS all_nationsGROUP BY o_yearORDER BY o_yearLIMIT 1;
查看更多
Q9:
SELECT nation, o_year, Sum(amount) AS sum_profitFROM ( SELECT n_name AS nation, Extract(year FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE s_suppkey = l_suppkey AND ps_suppkey = l_suppkey AND ps_partkey = l_partkey AND p_partkey = l_partkey AND o_orderkey = l_orderkey AND s_nationkey = n_nationkey AND p_name LIKE '%aquamarine%' ) AS profitGROUP BY nation, o_yearORDER BY nation, o_year DESCLIMIT 1;
查看更多
Q10:
SELECT c_custkey, c_name, Sum(l_extendedprice * (1 - l_discount)) AS revenue, c_acctbal, n_name, c_address, c_phone, c_commentFROM customer, orders, lineitem, nationWHERE c_custkey = o_custkeyAND l_orderkey = o_orderkeyAND o_orderdate >= date '1994-08-01'AND o_orderdate < date '1994-08-01' + INTERVAL '3' monthAND l_returnflag = 'R'AND c_nationkey = n_nationkeyGROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_commentORDER BY revenue DESCLIMIT 20;
查看更多
Q11:
SELECT ps_partkey, Sum(ps_supplycost * ps_availqty) AS valueFROM partsupp, supplier, nationWHERE ps_suppkey = s_suppkeyAND s_nationkey = n_nationkeyAND n_name = 'PERU'GROUP BY ps_partkeyHAVING Sum(ps_supplycost * ps_availqty) > (SELECT Sum(ps_supplycost * ps_availqty) * 0.0001000000 as sum_value FROM partsupp, supplier, nation WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = 'PERU')ORDER BY value DESCLIMIT 1;
Q12:
SELECT l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0end) AS high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1else 0end) AS low_line_countFROM orders, lineitemWHERE o_orderkey = l_orderkeyAND l_shipmode in ('MAIL', 'TRUCK')AND l_commitdate < l_receiptdateAND l_shipdate < l_commitdateAND l_receiptdate >= date '1996-01-01'AND l_receiptdate < date '1996-01-01' + interval '1' yearGROUP BY l_shipmodeORDER BY l_shipmodeLIMIT 1;
Q13:
SELECT c_count, count(*) AS custdistFROM ( SELECT c_custkey, count(o_orderkey) AS c_count FROM customer, orders WHERE c_custkey = o_custkey AND o_comment NOT LIKE '%pending%accounts%' GROUP BY c_custkey ) AS c_ordersGROUP BY c_countORDER BY custdist DESC, c_count DESCLIMIT 1;
Q14:
SELECT 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0end) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenueFROM lineitem, partWHERE l_partkey = p_partkeyAND l_shipdate >= date '1996-01-01'AND l_shipdate < date '1996-01-01' + interval '1' monthLIMIT 1;
Q15:
WITH revenue0 AS (SELECT l_suppkey AS supplier_no, sum(l_extendedprice * (1 - l_discount)) AS total_revenueFROM lineitemWHERE l_shipdate >= date '1993-01-01'AND l_shipdate < date '1993-01-01' + interval '3' monthGROUP BY l_suppkey )SELECT s_suppkey, s_name, s_address, s_phone, total_revenueFROM supplier, revenue0WHERE s_suppkey = supplier_noAND total_revenue IN ( SELECT max(total_revenue) FROM revenue0 )ORDER BY s_suppkey;
Q16:
SELECT p_brand, p_type, p_size, count(distinct ps_suppkey) AS supplier_cntFROM partsupp, partWHERE p_partkey = ps_partkeyAND p_brand <> 'Brand#23'AND p_type NOT LIKE 'PROMO BURNISHED%'AND p_size IN (1, 13, 10, 28, 21, 35, 31, 11)AND ps_suppkey NOT IN ( SELECT s_suppkey FROM supplier WHERE s_comment LIKE '%Customer%Complaints%' )GROUP BY p_brand, p_type, p_sizeORDER BY supplier_cnt DESC, p_brand, p_type, p_sizeLIMIT 1;
Q17:
SELECT sum(l_extendedprice) / 7.0 AS avg_yearlyFROM lineitem, partWHERE p_partkey = l_partkey AND p_brand = 'Brand#44' AND p_container = 'WRAP PKG' AND l_quantity < ( SELECT 0.2 * avg(l_quantity) FROM lineitem, part WHERE l_partkey = p_partkey );
Q18:
SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)FROM customer, orders, lineitemWHERE o_orderkey IN ( SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING sum(l_quantity) > 315 )AND c_custkey = o_custkeyAND o_orderkey = l_orderkeyGROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalpriceORDER BY o_totalprice DESC, o_orderdateLIMIT 100;
Q19:
SELECT sum(l_extendedprice* (1 - l_discount)) AS revenueFROM lineitem, partWHERE ( p_partkey = l_partkey and p_brand = 'Brand#12' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 6 and l_quantity <= 6 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#13' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 10 and l_quantity <= 10 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#24' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 21 and l_quantity <= 21 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )LIMIT 1;
查看更多
Q20:
with temp_table as ( select 0.5 * sum(l_quantity) as col1 from lineitem, partsupp where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date '1993-01-01' and l_shipdate < date '1993-01-01' + interval '1' year)select s_name, s_addressfrom supplier, nationwhere s_suppkey in ( select ps_suppkey from partsupp, temp_table where ps_partkey in ( select p_partkey from part where p_name like 'dark%' ) and ps_availqty > temp_table.col1 ) and s_nationkey = n_nationkey and n_name = 'JORDAN'order by s_namelimit 1;
Q21:
select s_name, count(*) as numwaitfrom supplier, lineitem l1, orders, nationwhere s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'SAUDI ARABIA'group by s_nameorder by numwait desc, s_namelimit 100;
查看更多
Q22:
with temp_table_1 as ( select avg(c_acctbal) as avg_value from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('33', '29', '37', '35', '25', '27', '43') ), temp_table_2 as( select count(*) as count1 from orders, customer where o_custkey = c_custkey )select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbalfrom ( select substring(c_phone from 1 for 2) as cntrycode, c_acctbal from customer, temp_table_1, temp_table_2 where substring(c_phone from 1 for 2) in ('33', '29', '37', '35', '25', '27', '43') and c_acctbal > temp_table_1.avg_value and temp_table_2.count1 = 0) as custsalegroup by cntrycodeorder by cntrycodelimit 1;
Data Lake Analytics支持“同步執(zhí)行”模式和“異步執(zhí)行”模式?!巴綀?zhí)行”模式下,控制臺(tái)界面等待執(zhí)行結(jié)果返回;“異步執(zhí)行”模式下,立刻返回查詢?nèi)蝿?wù)的ID。
點(diǎn)擊“執(zhí)行狀態(tài)”,可以看到該異步查詢?nèi)蝿?wù)的執(zhí)行狀態(tài),主要分為:“RUNNING”,“SUCCESS”,“FAILURE”。
點(diǎn)擊“刷新”,當(dāng)STATUS變?yōu)椤癝UCCESS”時(shí),表示查詢成功,同時(shí)可查看查詢耗時(shí)“ELAPSE_TIME”和查詢掃描的數(shù)據(jù)字節(jié)數(shù)“SCANNED_DATA_BYTES”。
點(diǎn)擊“執(zhí)行歷史”,可以看到您執(zhí)行的查詢的歷史詳細(xì)信息,包括:
1)查詢語(yǔ)句;
2)查詢耗時(shí)與執(zhí)行具體時(shí)間;
3)查詢結(jié)果返回行數(shù);
4)查詢狀態(tài);
5)查詢掃描的字節(jié)數(shù);
6)結(jié)果集回寫到的目標(biāo)OSS文件(Data Lake Analytics會(huì)將查詢結(jié)果集保存用戶的bucket中)。
查詢結(jié)果文件自動(dòng)上傳到用戶同region的OSS bucket中,其中包括結(jié)果數(shù)據(jù)文件和結(jié)果集元數(shù)據(jù)描述文件。
{QueryLocation}/{query_name}|Unsaved}/{yyyy}/{mm}/{dd}/{query_id}/xxx.csv {QueryLocation}/{query_name}|Unsaved}/{yyyy}/{mm}/{dd}/{query_id}/xxx.csv.metadata
其中QueryLocation為:
aliyun-oa-query-results-<your_account_id>-<oss_region>
看完上述內(nèi)容是否對(duì)您有幫助呢?如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章,請(qǐng)關(guān)注億速云行業(yè)資訊頻道,感謝您對(duì)億速云的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。