This topic describes how to query data in foreign tables and internal tables in Hologres. This helps you experience the performance of data queries in Hologres.
Prerequisites
- A Hologres instance is purchased. For more information, see Purchase a Hologres instance.
- A RAM user that you want to use to perform operations is granted the required permissions. For more information, see Grant permissions to a RAM user.
Background information
Hologres can give quick response when you query data. This topic shows you how to create a database, create foreign tables and internal tables, and then import data to the internal tables in Hologres. Then, this topic shows you how to query data in the foreign tables and internal tables in Hologres. This helps you experience data queries in Hologres.
Procedure
Step 1: Create a database
Step 2: Create tables
After the database is created, you can create tables in the database. You can create foreign tables or internal tables based on where your data is stored.
- Create foreign tables.
- Log on to the database.
- On the Database Authorization page of the HoloWeb console, click Metadata Management in the top navigation bar.
- On the Metadata Management tab, find the created database in the left-side navigation pane and double-click
the database name. In the dialog box that appears, click OK.
- Create foreign tables.
- Go to the SQL Editor tab. Click the Ad-hoc Query icon in the upper-left corner.
- On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then
click Run.
The following SQL statements are used to create foreign tables for later queries. The foreign tables map fields in the source tables such as the odps_customer_10g and odps_lineitem_10g tables in the MaxCompute project MAXCOMPUTE_PUBLIC_DATA in the public dataset.
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 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');
- Log on to the database.
- Create internal tables.
- Log on to the database.
- On the Database Authorization page of the HoloWeb console, click Metadata Management in the top navigation bar.
- On the Metadata Management tab, find the created database in the left-side navigation pane and double-click
the database name. In the dialog box that appears, click OK.
- Create internal tables.
- Go to the SQL Editor tab. Click the Ad-hoc Query icon in the upper-left corner.
- On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then
click Run.
The following SQL statements are used to create internal tables named LINEITEM, ORDERS, PARTSUPP, PART, CUSTOMER, SUPPLIER, NATION, and REGION. The internal tables are used to store data for later queries.
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 TIMESTAMPTZ NOT NULL, L_COMMITDATE TIMESTAMPTZ NOT NULL, L_RECEIPTDATE TIMESTAMPTZ 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_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT'); CALL set_table_property('LINEITEM', 'dictionary_encoding_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT'); CALL set_table_property('LINEITEM', 'time_to_live_in_seconds', '31536000'); 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 timestamptz 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', 'distribution_key', 'O_ORDERKEY'); CALL set_table_property('ORDERS', 'bitmap_columns', 'O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT'); CALL set_table_property('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT'); CALL set_table_property('ORDERS', 'time_to_live_in_seconds', '31536000'); COMMIT; DROP TABLE IF EXISTS PARTSUPP; BEGIN; CREATE TABLE PARTSUPP ( PS_PARTKEY INT NOT NULL, PS_SUPPKEY INT NOT NULL, PS_AVAILQTY INT NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT TEXT NOT NULL, PRIMARY KEY(PS_PARTKEY,PS_SUPPKEY) ); CALL set_table_property('PARTSUPP', 'distribution_key', 'PS_PARTKEY'); CALL set_table_property('PARTSUPP', 'colocate_with', 'LINEITEM'); CALL set_table_property('PARTSUPP', 'bitmap_columns', 'PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_COMMENT'); CALL set_table_property('PARTSUPP', 'dictionary_encoding_columns', 'PS_COMMENT'); CALL set_table_property('PARTSUPP', 'time_to_live_in_seconds', '31536000'); COMMIT; DROP TABLE IF EXISTS PART; BEGIN; CREATE TABLE PART ( P_PARTKEY INT NOT NULL PRIMARY KEY, P_NAME TEXT NOT NULL, P_MFGR TEXT NOT NULL, P_BRAND TEXT NOT NULL, P_TYPE TEXT NOT NULL, P_SIZE INT NOT NULL, P_CONTAINER TEXT NOT NULL, P_RETAILPRICE DECIMAL(15,2) NOT NULL, P_COMMENT TEXT NOT NULL ); CALL set_table_property('PART', 'distribution_key', 'P_PARTKEY'); CALL set_table_property('PART', 'bitmap_columns', 'P_PARTKEY,P_SIZE,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT'); CALL set_table_property('PART', 'dictionary_encoding_columns', 'P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT'); CALL set_table_property('PART', 'time_to_live_in_seconds', '31536000'); 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', 'bitmap_columns', 'C_CUSTKEY,C_NATIONKEY,C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT'); CALL set_table_property('CUSTOMER', 'dictionary_encoding_columns', 'C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT'); CALL set_table_property('CUSTOMER', 'time_to_live_in_seconds', '31536000'); 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', 'bitmap_columns', 'S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_COMMENT'); CALL set_table_property('SUPPLIER', 'dictionary_encoding_columns', 'S_NAME,S_ADDRESS,S_PHONE,S_COMMENT'); CALL set_table_property('SUPPLIER', 'time_to_live_in_seconds', '31536000'); 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', 'bitmap_columns', 'N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT'); CALL set_table_property('NATION', 'dictionary_encoding_columns', 'N_NAME,N_COMMENT'); CALL set_table_property('NATION', 'time_to_live_in_seconds', '31536000'); 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', 'bitmap_columns', 'R_REGIONKEY,R_NAME,R_COMMENT'); CALL set_table_property('REGION', 'dictionary_encoding_columns', 'R_NAME,R_COMMENT'); CALL set_table_property('REGION', 'time_to_live_in_seconds', '31536000'); COMMIT;
- Log on to the database.
Step 3: Import sample data
After the internal tables are created, you can perform the following steps to import data to the internal tables in Hologres.