建设淘宝客网站,有什么做美食的视频网站,premium wordpress themes,网站建设玖金手指花总文章目录
Hologres 快速入门
一、资源领取
二、入门体验
1、创建数据库
2、创建表
3、导入示例数据
4、查询表中数据 Hologres 快速入门
一、资源领取
领取链接#xff1a; 阿里云免费试用 - 阿里云 (aliyun.com) 二、入门体验
1、创建数据库
进入Hologres管理控制…
文章目录
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_existerror,if_unsupported_typeerror);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);在Hologres中vacuum是一个数据库操作命令用于回收并释放数据库中不再需要的存储空间。具体而言vacuum 命令的作用通常包括 Analyze 也是一个数据库操作命令用于收集统计信息以帮助数据库系统优化查询计划。通过分析表的数据分布和结构数据库系统可以更好地选择执行计划提高查询性能。它通常执行以下操作 4、查询表中数据
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;博客主页https://lansonli.blog.csdn.net欢迎点赞 收藏 ⭐留言 如有错误敬请指正本文由 Lansonli 原创首发于 CSDN博客停下休息的时候不要忘了别人还在奔跑希望大家抓紧时间学习全力奔赴更美好的生活✨
