文章目錄

Hologres 快速入門

一、資源領(lǐng)取

二、入門體驗(yàn)

1、創(chuàng)建數(shù)據(jù)庫(kù)

2、創(chuàng)建表

3、導(dǎo)入示例數(shù)據(jù)

4、查詢表中數(shù)據(jù)

---

Hologres 快速入門

一、資源領(lǐng)取

領(lǐng)取鏈接： 阿里云免費(fèi)試用 - 阿里云 (aliyun.com)

二、入門體驗(yàn)

1、創(chuàng)建數(shù)據(jù)庫(kù)

• 進(jìn)入Hologres管理控制臺(tái)，單擊左側(cè)實(shí)例列表。
• 在實(shí)例列表頁(yè)面，單擊實(shí)例名稱

• 在頁(yè)面右上角點(diǎn)擊登錄實(shí)例，并新建數(shù)據(jù)庫(kù)。

2、創(chuàng)建表

2.1、創(chuàng)建外部表

• 選擇SQL編輯器

• 在新增的臨時(shí)Query查詢頁(yè)面，選擇已創(chuàng)建的實(shí)例名和數(shù)據(jù)庫(kù)?

• 選擇已創(chuàng)建的實(shí)例名和數(shù)據(jù)庫(kù)后，在SQL查詢的編輯框輸入如下語(yǔ)句，單擊運(yùn)行。

DROP FOREIGN TABLE IF EXISTS odps_customer_10g;
DROP FOREIGN TABLE IF EXISTS odps_lineitem_10g;
DROP FOREIGN TABLE IF EXISTS odps_nation_10g;
DROP FOREIGN TABLE IF EXISTS odps_orders_10g;
DROP FOREIGN TABLE IF EXISTS odps_part_10g;
DROP FOREIGN TABLE IF EXISTS odps_partsupp_10g;
DROP FOREIGN TABLE IF EXISTS odps_region_10g;
DROP FOREIGN TABLE IF EXISTS odps_supplier_10g;

IMPORT FOREIGN SCHEMA "MAXCOMPUTE_PUBLIC_DATA#default" LIMIT to 
(odps_customer_10g,odps_lineitem_10g,odps_nation_10g,odps_orders_10g,odps_part_10g,odps_partsupp_10g,odps_region_10g,odps_supplier_10g
) FROM SERVER odps_server INTO public OPTIONS(if_table_exist'error',if_unsupported_type'error');

?

2.2、創(chuàng)建內(nèi)部表

以下SQL語(yǔ)句用來(lái)創(chuàng)建名稱分別為L(zhǎng)INEITEM、ORDERS、PARTSUPP、PART、CUSTOMER、SUPPLIER、NATION和REGION的表，用于后續(xù)存儲(chǔ)數(shù)據(jù)。

DROP TABLE IF EXISTS LINEITEM;BEGIN;
CREATE TABLE LINEITEM (L_ORDERKEY bigint NOT NULL,L_PARTKEY int NOT NULL,L_SUPPKEY int NOT NULL,L_LINENUMBER int NOT NULL,L_QUANTITY DECIMAL(15, 2) NOT NULL,L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,L_DISCOUNT DECIMAL(15, 2) NOT NULL,L_TAX DECIMAL(15, 2) NOT NULL,L_RETURNFLAG text NOT NULL,L_LINESTATUS text NOT NULL,L_SHIPDATE date NOT NULL,L_COMMITDATE date NOT NULL,L_RECEIPTDATE date NOT NULL,L_SHIPINSTRUCT text NOT NULL,L_SHIPMODE text NOT NULL,L_COMMENT text NOT NULL,PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
);

DROP TABLE IF EXISTS LINEITEM;BEGIN;
CREATE TABLE LINEITEM (L_ORDERKEY bigint NOT NULL,L_PARTKEY int NOT NULL,L_SUPPKEY int NOT NULL,L_LINENUMBER int NOT NULL,L_QUANTITY DECIMAL(15, 2) NOT NULL,L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,L_DISCOUNT DECIMAL(15, 2) NOT NULL,L_TAX DECIMAL(15, 2) NOT NULL,L_RETURNFLAG text NOT NULL,L_LINESTATUS text NOT NULL,L_SHIPDATE date NOT NULL,L_COMMITDATE date NOT NULL,L_RECEIPTDATE date NOT NULL,L_SHIPINSTRUCT text NOT NULL,L_SHIPMODE text NOT NULL,L_COMMENT text NOT NULL,PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
);

CALL set_table_property ('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY');
CALL set_table_property ('LINEITEM', 'segment_key', 'L_SHIPDATE');
CALL set_table_property ('LINEITEM', 'distribution_key', 'L_ORDERKEY');
CALL set_table_property ('LINEITEM', 'bitmap_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE');
CALL set_table_property ('LINEITEM', 'dictionary_encoding_columns', 'l_comment:off,l_returnflag,l_linestatus,l_shipinstruct,l_shipmode');
COMMIT;DROP TABLE IF EXISTS ORDERS;BEGIN;
CREATE TABLE ORDERS (O_ORDERKEY bigint NOT NULL PRIMARY KEY,O_CUSTKEY int NOT NULL,O_ORDERSTATUS text NOT NULL,O_TOTALPRICE DECIMAL(15, 2) NOT NULL,O_ORDERDATE date NOT NULL,O_ORDERPRIORITY text NOT NULL,O_CLERK text NOT NULL,O_SHIPPRIORITY int NOT NULL,O_COMMENT text NOT NULL
);
CALL set_table_property ('ORDERS', 'segment_key', 'O_ORDERDATE');
CALL set_table_property ('ORDERS', 'colocate_with', 'lineitem');
CALL set_table_property ('ORDERS', 'distribution_key', 'O_ORDERKEY');
CALL set_table_property ('ORDERS', 'bitmap_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY');
CALL set_table_property ('ORDERS', 'dictionary_encoding_columns', 'o_comment:off,o_orderstatus,o_orderpriority,o_clerk');
COMMIT;

DROP TABLE IF EXISTS CUSTOMER;BEGIN;
CREATE TABLE CUSTOMER (C_CUSTKEY int NOT NULL PRIMARY KEY,C_NAME text NOT NULL,C_ADDRESS text NOT NULL,C_NATIONKEY int NOT NULL,C_PHONE text NOT NULL,C_ACCTBAL DECIMAL(15, 2) NOT NULL,C_MKTSEGMENT text NOT NULL,C_COMMENT text NOT NULL
);
CALL set_table_property ('CUSTOMER', 'distribution_key', 'C_CUSTKEY');
CALL set_table_property ('CUSTOMER', 'colocate_with', 'lineitem');
CALL set_table_property ('CUSTOMER', 'bitmap_columns', 'C_NATIONKEY,C_MKTSEGMENT');
CALL set_table_property ('CUSTOMER', 'dictionary_encoding_columns', 'c_name:off,c_address:off,c_phone:off,c_comment:off,c_mktsegment');
COMMIT;

DROP TABLE IF EXISTS SUPPLIER;BEGIN;
CREATE TABLE SUPPLIER (S_SUPPKEY int NOT NULL PRIMARY KEY,S_NAME text NOT NULL,S_ADDRESS text NOT NULL,S_NATIONKEY int NOT NULL,S_PHONE text NOT NULL,S_ACCTBAL DECIMAL(15, 2) NOT NULL,S_COMMENT text NOT NULL
);
CALL set_table_property ('SUPPLIER', 'distribution_key', 'S_SUPPKEY');
CALL set_table_property ('SUPPLIER', 'colocate_with', 'lineitem');
CALL set_table_property ('SUPPLIER', 'bitmap_columns', 'S_NATIONKEY');
CALL set_table_property ('SUPPLIER', 'dictionary_encoding_columns', '');
COMMIT;DROP TABLE IF EXISTS NATION;BEGIN;
CREATE TABLE NATION (N_NATIONKEY int NOT NULL PRIMARY KEY,N_NAME text NOT NULL,N_REGIONKEY int NOT NULL,N_COMMENT text NOT NULL
);

CALL set_table_property ('NATION', 'distribution_key', 'N_NATIONKEY');
CALL set_table_property ('NATION', 'colocate_with', 'lineitem');
CALL set_table_property ('NATION', 'bitmap_columns', '');
CALL set_table_property ('NATION', 'dictionary_encoding_columns', '');
COMMIT;DROP TABLE IF EXISTS REGION;BEGIN;
CREATE TABLE REGION (R_REGIONKEY int NOT NULL PRIMARY KEY,R_NAME text NOT NULL,R_COMMENT text
);
CALL set_table_property ('REGION', 'distribution_key', 'R_REGIONKEY');
CALL set_table_property ('REGION', 'colocate_with', 'lineitem');
CALL set_table_property ('REGION', 'bitmap_columns', '');
CALL set_table_property ('REGION', 'dictionary_encoding_columns', '');
COMMIT;

注意事項(xiàng)：

外部表在Hologres中不存儲(chǔ)數(shù)據(jù)，只進(jìn)行字段映射。通過外部表可以使用Hologres直接調(diào)用存儲(chǔ)于MaxCompute公共空間MAXCOMPUTE_PUBLIC_DATA的數(shù)據(jù)。?

3、導(dǎo)入示例數(shù)據(jù)

• SQL查詢的編輯框輸入如下語(yǔ)句，單擊運(yùn)行。

INSERT INTO public.customer SELECT * FROM public.odps_customer_10g ;
INSERT INTO public.lineitem SELECT * FROM public.odps_lineitem_10g ;
INSERT INTO public.nation SELECT * FROM public.odps_nation_10g ;
INSERT INTO public.orders SELECT * FROM public.odps_orders_10g ;
INSERT INTO public.part SELECT * FROM public.odps_part_10g ;
INSERT INTO public.partsupp SELECT * FROM public.odps_partsupp_10g ;
INSERT INTO public.region SELECT * FROM public.odps_region_10g ;
INSERT INTO public.supplier SELECT * FROM public.odps_supplier_10g ;vacuum nation;
vacuum region;vacuum supplier;
vacuum supplier;
vacuum customer;
vacuum part;
vacuum partsupp;
vacuum orders;
vacuum lineitem;analyze nation;
analyze region;
analyze lineitem;
analyze orders;
analyze customer;
analyze part;
analyze partsupp;
analyze supplier;
analyze lineitem (l_orderkey,l_partkey,l_suppkey);
analyze orders (o_custkey);
analyze partsupp(ps_partkey,ps_suppkey);

• 在Hologres中，vacuum是一個(gè)數(shù)據(jù)庫(kù)操作命令，用于回收并釋放數(shù)據(jù)庫(kù)中不再需要的存儲(chǔ)空間。
• 具體而言，vacuum 命令的作用通常包括：

• Analyze 也是一個(gè)數(shù)據(jù)庫(kù)操作命令，用于收集統(tǒng)計(jì)信息，以幫助數(shù)據(jù)庫(kù)系統(tǒng)優(yōu)化查詢計(jì)劃。通過分析表的數(shù)據(jù)分布和結(jié)構(gòu)，數(shù)據(jù)庫(kù)系統(tǒng)可以更好地選擇執(zhí)行計(jì)劃，提高查詢性能。
• 它通常執(zhí)行以下操作：?

4、查詢表中數(shù)據(jù)

selectl_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_order
From lineitem
Where l_shipdate <= date '1998-12-01' - interval '120' day
group by l_returnflag, l_linestatus
order byl_returnflag,l_linestatus;

---

• 📢博客主頁(yè)：https://lansonli.blog.csdn.net
• 📢歡迎點(diǎn)贊 👍 收藏 ?留言 📝 如有錯(cuò)誤敬請(qǐng)指正！
• 📢本文由 Lansonli 原創(chuàng)，首發(fā)于 CSDN博客🙉
• 📢停下休息的時(shí)候不要忘了別人還在奔跑，希望大家抓緊時(shí)間學(xué)習(xí)，全力奔赴更美好的生活?