文章目录
Hologres%20%E5%BF%AB%E9%80%9F%E5%85%A5%E9%97%A8-toc" style="margin-left:0px;">Hologres 快速入门
一、资源领取
二、入门体验
1、创建数据库
2、创建表
3、导入示例数据
4、查询表中数据
Hologres 快速入门
一、资源领取
领取链接: 阿里云免费试用 - 阿里云 (aliyun.com)
二、入门体验
1、创建数据库
- 进入Hologres管理控制台,单击左侧实例列表。
- 在实例列表页面,单击实例名称
- 在页面右上角点击登录实例,并新建数据库。
2、创建表
2.1、创建外部表
- 选择SQL编辑器
- 在新增的临时Query查询页面,选择已创建的实例名和数据库
- 选择已创建的实例名和数据库后,在SQL查询的编辑框输入如下语句,单击运行。
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、创建内部表
以下SQL语句用来创建名称分别为LINEITEM、ORDERS、PARTSUPP、PART、CUSTOMER、SUPPLIER、NATION和REGION的表,用于后续存储数据。
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;
注意事项:
外部表在Hologres中不存储数据,只进行字段映射。通过外部表可以使用Hologres直接调用存储于MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA的数据。
3、导入示例数据
- SQL查询的编辑框输入如下语句,单击运行。
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);
4、查询表中数据
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_order
From lineitem
Where l_shipdate <= date '1998-12-01' - interval '120' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
- 📢博客主页:https://lansonli.blog.csdn.net
- 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正!
- 📢本文由 Lansonli 原创,首发于 CSDN博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑,希望大家抓紧时间学习,全力奔赴更美好的生活✨