Shulou(Shulou.com)06/03 Report--

one。 TPC-H compiles 1. Download tool TPC-H

Download address

two。 Copy to generate makefile and modify makefile/opt/tpc-h-v2.17.0/dbgencp makefile.suite makefile

Modify makefile

3. Compile make-f makefile

4. Generate data dbgen-v-U 1-s 1

. / dbgen

5. Create the database and related table statements-- create database tpch;\ c tpch;--1. Regiondrop table if exists region;create table region (r_regionkey integer,r_name char (25), r_comment varchar (152), r_extra char (1)) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (r_regionkey); Nationdrop table if exists nation;create table nation (n_nationkey integer,n_name char (25), n_regionkey integer,n_comment varchar (152), n_extra char (1)) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (n_nationkey); 3.partdrop table if exists part Create table part (p_partkey bigint,p_name varchar (55), p_mfgr char (25), p_brand char (10), p_type varchar (25), p_size integer,p_container varchar (10), p_retailprice decimal,p_comment varchar (117), p_extra char (1) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (p_partkey);-- 4.supplierdrop table if exists supplier Create table supplier (s_suppkey bigint,s_name char (25), s_address varchar (40), s_nationkey int,s_phone char (15), s_acctbal decimal,s_comment varchar (101), r_extra char (1) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (s_suppkey);-- 5.partsuppdrop table if exists partsupp Create table partsupp (ps_partkey bigint,ps_suppkey bigint,ps_availqty integer,ps_supplycost decimal,ps_comment varchar (199), ps_extra char (1)) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (ps_partkey); # 6.customerdrop table if exists customer Create table customer (c_custkey bigint,c_name char (25), c_address char (40), c_nationkey integer,c_phone char (15), c_acctbal decimal,c_mktsegment char (10), c_comment varchar (117), c_extra char (1) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (c_custkey);-- 7.ordersdrop table if exists orders Create table orders (o_orderkey bigint,o_custkey bigint,o_orderstatus char (1), o_totalprice decimal,o_orderdate date,o_orderpriority char (15), o_clerk char (15), o_shippriority integer,o_comment varchar (117), o_extra char (1) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (o_orderkey);-- 8.lineitemdrop table if exists lineitem Create table lineitem (l_orderkey bigint,l_partkey bigint,l_suppkey bigint,l_linenumber integer,l_quantity decimal,l_extendedprice decimal,l_discount decimal,l_tax decimal,l_returnflag char (1), l_linestatus char (1), l_shipdate date,l_commitdate date,l_receiptdate date,l_shipinstruct char (25), l_shipmode char (10), l_comment varchar (117), l_extra char (1) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by (l_linenumber); 6. Query statement in the queries directory 22 II, create tablespace 1. Create a filespace configuration file and execute $hawq filespace-o tpc_h_config on the master node

The contents of the document are as follows:

Filespace:fs_tpc_hfsreplica:3dfs_url::mycluster/hawq_fs/fs_tpc_h2. Create the HDFS directory $hdfs dfs-mkdir / hawq_fs$hdfs dfs-chown gpadmin:gpadmin / hawq_fs$hdfs dfs-ls /

3. Create a filespace $hawq filespace-c tpc_h_config

4. Create tablespaces, psqlcreate tablespace ts_tpc_h filespace fs_tpc_h

5. Delete filespace and tablespace

   tablespace owners can delete, and no other data objects (such as databases, tables) can use the current tablespace, and no tablespace can use the current filespace.

6. View all current tablespaces SELECT spcname AS tblspc, fsname AS filespc, fsedbid AS seg_dbid, fselocation AS datadir FROM pg_tablespace pgts, pg_filespace pgfs, pg_filespace_entry pgfse WHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY tblspc, seg_dbid

Third, create the database and Schema1. Create a database create database tpc_h with template template0 tablespace ts_tpc_h

\ C tpc_h;2. Create schemacreate schema extschema

3. To view the current schema, the function current_schema () select current_schema ()

4. View the current Schema search path show search_path

5. Specify schema to create the data table create external table extschema.region (r_regionkey integer,r_name char (25), r_comment varchar (152), r_extra char (1)) location ('gpfdist://10.110.17.104:8081/region.tbl') format' text' (delimiter'| 'null' 'escape' OFF') encoding 'UTF8'

6. Modify the search path of SCHEMA: ALTER DATABASE tpc_h SET search_path TO extschema, public

Exit and restart into the psql client

Fourth, create a data table

Execute the script in Chapter 1.

Fifth, create the view drop view if exists v_order_customer; create view v_order_customeras select o.o_orderkey, o.o_custkey, c. C. The name of the book is not available. The date from orders o join customer c on o.o_custkey=c.c_custkeywhere o.o_orderdate > '1997-01-01'.

Select * from v_order_customer limit 10 * select * from v_order_customer where o_orderdate / tmp/pxf_hdfs_simple.txt3. Upload the file to hdfshdfs dfs-put / tmp/pxf_hdfs_simple.txt / hawq_fs/pxf_data/hdfs dfs-cat / hawq_fs/pxf_data/pxf_hdfs_simple.txt

Select * from pxf_hdfs_textsimple

ten。 The PXF plug-in loads HIVE data 1. Create hawq data table drop table if exists salesinfo;create table salesinfo (location text, month text, num_orders int, total_sales float8) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed randomly;1. Hive create table sales_info/usr/hdp/current/hive-client/bin./hivedrop table if exists sales_info;create table sales_info (location string, month string, number_of_orders int, total_sales double) row format delimited fields terminated by', 'stored as textfile Prague,Jan,101,4875.33Rome,Mar,87,1557.39Bangalore,May,317,8936.99Beijing,Jul,411,11600.67San Francisco,Sept,156,6846.34Paris,Nov,159,7134.56San Francisco,Jan,113,5397.89Prague,Dec,333,9894.77Bangalore,Jul,271,8320.55Beijing,Dec,100,4248.412. Load data to hive data table load data local inpath'/ tmp/pxf_hive_datafile.txt' into table sales_info

Method 1: create an external table

Psql

1. Create external table mode drop table if exists salesinfo_hiveprofile;create external table salesinfo_hiveprofile (location text, month text, num_orders int, total_sales float8) location ('pxf://mycluster/default.sales_info?profile=hive') format' custom' (formatter='pxfwritable_import')

two。 Import data insert into salesinfo select * from salesinfo_hiveprofile

Method 2: read hive Hcatalog metadata service

In this way, we need to modify the content of Hive in pxf-profiles.xml and add outputformat objects.

HiveText:

Org.apache.hawq.pxf.service.io.Text

HiveORC:

Org.apache.hawq.pxf.service.io.GPDBWritable

HiveRC:

Org.apache.hawq.pxf.service.io.Text

Hive

Org.apache.hawq.pxf.service.io.GPDBWritable

Format:

SELECT * FROM hcatalog.hive-db-name.hive-table-name

Such as:

SELECT * FROM hcatalog.default.sales_info

Import data:

Insert into salesinfo SELECT * FROM hcatalog.default.sales_info

eleven。 The PXF plug-in loads HBASE data-- mode 1, directly referencing column families and Qualifier1. Create an external table drop EXTERNAL table if exists hbase_sales;create external table hbase_sales (recordkey bytea, "cf1:saleid" varchar, "cf8:comments" varchar) location ('pxf://mycluster/sales?profile=hbase') format' custom' (formatter='pxfwritable_import') encoding 'utf8';2. Use HBase Shell to create a HBase data table and write data cd / usr/hdp/current/hbase-client/bin./hbase shell

Create a table

Create 'sales', {NAME= >' cf1',VERSION= > 2}, {NAME= > 'cf8',VERSION= > 2} # create' pxf_hbase_region', {NAME= > 'cf1',VERSION= > 2}

Write data

Put 'sales','rk001','cf1:saleid',' s001'put 'sales','rk001','cf8:comments',' comments1'select * from hbase_sales

Method 2: create pxflookup table create 'pxflookup', {NAME= >' mapping',VERSION= > 2} put 'pxflookup',' sales', 'mapping:id',' cf1:saleid'put 'pxflookup',' sales', 'mapping:cmts',' cf8:comments'drop EXTERNAL table if exists pxf_hbase_sales in HBase CREATE EXTERNAL TABLE pxf_hbase_sales (recordkey bytea, id varchar, cmts varchar) location ('pxf://mycluster/sales?profile=hbase') format' custom' (formatter='pxfwritable_import') encoding 'utf8';select * from pxf_hbase_sales; 12. PXF visits Jdbc (mysql) grant all privileges on *. * to "pxf" @ "%" identified by 'test'; mysql > use test; mysql > create table myclass (id int (4) not null primary key, name varchar (20) not null, gender int (4) not null default' 0mm, degree double (16jue 2)); insert into myclass values (1, "tom", 1); insert into myclass values (2) Insert into myclass values (3 records1 pas 79)

/ etc/pxf/conf/pxf-public.classpath add / usr/lib/pxf/mysql-connector-java-*.jar

Psql:

Gpadmin=#drop external table if exists jdbc_myclass CREATE EXTERNAL TABLE jdbc_myclass (id integer, name text, gender integer Degree float8) LOCATION ('pxf://localhost:51200/test.myclass''? PROFILE=JDBC''& JDBC_DRIVER=com.mysql.jdbc.Driver''& DB_URL=jdbc:mysql://10.110.22.191:3306/test&USER=pxf&PASS=test') FORMAT 'CUSTOM' (Formatter='pxfwritable_import') Select * from jdbc_myclass

Slaes_info:

Create table sales_info (location varchar, month varchar, number_of_orders int, total_sales double) Insert into sales_info values ("Prague", "Jan", 101pr 4875.33), ("Rome", "Mar", 87pr 1557.39), ("Bangalore", "May", 317pr 8936.99), ("Beijing", "Jul", 411 mahr 11600.67), ("San Francisco", "Sept", 156pr 6846.34), ("Paris", "Nov", 159Q 7134.56), ("San Francisco", "Jan", 113J 5397.89), ("Prague", "Dec", 333 9894.77), ("Bangalore", "Jul"), ("Bangalore", "Jul" 271pr 8320.55), ("Beijing", "Dec", 100pr 4248.41)

Psql:

Drop external table if exists jdbc_sales_info CREATE EXTERNAL TABLE jdbc_sales_info (location varchar, month varchar, number_of_orders integer) Total_sales float8) LOCATION ('pxf://localhost:51200/test.sales_info''? PROFILE=JDBC''& JDBC_DRIVER=com.mysql.jdbc.Driver''& DB_URL=jdbc:mysql://10.110.22.191:3306/test&USER=pxf&PASS=test') FORMAT 'CUSTOM' (Formatter='pxfwritable_import') CREATE EXTERNAL TABLE sales (id integer, cdate date, amt float8 Grade text) LOCATION ('pxf://localhost:51200/sales''? PROFILE=JDBC''& JDBC_DRIVER=com.mysql.jdbc.Driver''& DB_URL=jdbc:mysql://192.168.200.6:3306/demodb&USER=root&PASS=root''& PARTITION_BY=cdate: Date&RANGE=2008-01-01 CUSTOM' 2010-01-01 January 2010 (Formatter='pxfwritable_import') thirteen。 Gpfdist unload data method 1: gpfdist defines a writable external table based on files. Define writable external table create writable external table unload_region (like region) location ('gpfdist://10.110.17.104:8081/exp_region.tbl') format' text' (delimiter',')

-- writable only, not readable

two。 Import data insert into unload_region select * from region

View the exported data file exp_region.tblcat exp_region.tbl

Method 2 use copy to unload data

Copy region

To'/ home/gpadmin/region.out'

Copy (select * from region where r_regionkey=1) to'/ home/gpadmin/region.out'

fourteen。 The PXF plug-in writes data to HDFS1. Create writable external table create writable external table pxf_hdfs_writabletbl_1 (location text, month text, num_orders int, total_sales float8) location ('pxf://mycluster/hawq_fs/pxf_data/pxfwritable_hdfs_textsimple1?profile=hdfstextsimple') format' text' (delimiter=e','); 2. Write data insert into pxf_hdfs_writabletbl_1 values ('frankfurt',' mar', 777, 3956.98); insert into pxf_hdfs_writabletbl_1 values ('cleveland',' oct', 3812, 96645.37)

3. You can only write but not read.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.