Sql ON Anywhere之数据篇
Sql ON Anywhere之数据篇
1 概述
当前用于大数据处理的引擎组件种类繁多，且各自提供了丰富的接口供用户使用。但对传统数据库用户来说，SQL语言依然是最熟悉和方便的一种接口。如果能在一个客户端中使用SQL语句操作不同的大数据组件，将极大提升使用各种大数据组件的效率。
GaussDB(DWS)支持SQL on Anywhere，基于GaussDB(DWS)可以操作OBS、Hadoop、Oracle、Spark和other GaussDB(DWS)，构筑起统一的大数据计算平台。主要包括基于文件系统(HDFS和OBS，狭义的SQL On Anywhere)和其他异构数据库的交互(ORACLE、SPARK和Other GaussDB)。基于文件系统的访问主要通过Foreign Table或者ELK机制的跨集群访问数据，与其他异构数据库主要通过EC+ODBC的方式访问。
SQL on Anywhere相关的介绍分几期介绍，此篇主要介绍基于HADOOP的数据生成、下载和上传，方便读写的模测。目前支持的格式主要是txt、csv、parquet、orc。
2 HDFS上数据上传下载
（1）安装hdfs客户端
source /opt/hadoopclient/bigdata_env
kinit hdfs
Password for hdfs@HADOOP.COM:（输入密码）
（2）查看数据
hdfs dfs -ls /user/hive/warehouse/
（3）拷贝HDFS数据
hdfs dfs -cp /user/hive/warehouse/mppdb/region /user/hive/warehouse/hdfsdata
（4）get数据到本地
hdfs dfs -get /user/hive/warehouse/mppdb/region /data1
（5）上传数据至HDFS
hdfs dfs -put /data1/region /user/hive/warehouse/mppdb/region
3 TXT/CSV数据生成
TXT/CSV格式可以通过第三方工具或者自己写程序构造，上传至hadoop或者在借助Hive生成，Hive生成类似parquet数据成功，在parquet章节介绍。
4 orc格式数据生成
4.1借助hdfs外表导出功能生成orc数据
（1）创建一张行存表region，并插入数据
（2）创建一张同结构的hdfs写外表
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS
(address 'xx,
hdfscfgpath 'xx',
type 'HDFS'
) ;
CREATE FOREIGN TABLE ft_wo_region
(
like region
)
SERVER
hdfs_server
OPTIONS
(
FORMAT 'orc',
encoding 'utf8',
FOLDERNAME '/user/hive/warehouse/mppdb/regin_orc/'
)
WRITE ONLY;
（3）导出orc数据
INSERT INTO ft_wo_regin SELECT * FROM region;
（4）orc数据存储于HDFS的/user/hive/warehouse/mppdb/regin_orc目录下
4.2通过Hive生成orc数据
生成步骤参考parquet数据生成。
5 parquet格式数据生成
5.1生成少量的数据
在Hive上创建parquet的表，手动插入数据，生成的数据存储于HDFS
（1）创建parquet表
drop table pt_region;
create table pt region
(
R_REGIONKEY INT,
R_NAME string,
R_COMMENT string)
stored as parquet;
（2）插入数据
INSERT INTO pt_region VALUES (1,’gaoxin’,’city’);
（3）数据存在于HDFS /user/hive/warehouse/pt_region
5.2 利用INSERT SELTCT其它格式表生成大量的数据
（1）将text数据put到hive上
hdfs dfs –put /mnt/data/customer_address /user/hive/warehouse/
（2）hive上创建text表（source /opt/hadoopclient/bigdata_env; kinit hdfs;beeline登录hive）
create table txt_customer_address
(
ca_address_sk int ,
ca_address_id char(16) ,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset decimal(5,2) ,
ca_location_type char(20)
)
row format delimited fields terminated by ',' stored as textfile ;
（3）load数据
load data inpath '/user/hive/warehouse/customer_address' into table txt_customer_address;
（4）创建parquet格式表
drop table pt_customer_address;
create table pt_customer_address
(
ca_address_sk int ,
ca_address_id char(16) ,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset decimal(5,2) ,
ca_location_type char(20)
)
stored as parquet;
（5）通过insert parquet表 select from text表的方式导入数据
insert into pt_customer_address select * from txt_customer_address;
（6）在hdfs上查看该parquet格式的表数据
hdfs dfs -ls /user/hive/warehouse/pt_customer_address
5.3带分区的parquet数据生成
（1）hive上创建orc表，并load数据
drop table orc_web_site;
create table orc_web_site
(
web_site_sk int ,
web_site_id char(16) ,
web_rec_start_date timestamp ,
web_rec_end_date timestamp ,
web_name varchar(50) ,
web_open_date_sk int ,
web_close_date_sk int ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id int ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id int ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2)
)
stored as orc;
load data inpath '/user/hive/warehouse/hdfs/public.orc_web_site' into table orc_web_site;
（2）创建parquet分区表，web_rec_start_date列为分区表（hive上分区列也是也是表的一列，不能在create table()中重复创建）
drop table pt_web_site;
create table pt_web_site
(
web_site_sk int ,
web_site_id char(16) ,
web_rec_end_date timestamp ,
web_name varchar(50) ,
web_open_date_sk int ,
web_close_date_sk int ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id int ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id int ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2)
)
PARTITIONED BY(web_rec_start_date timestamp)
stored as parquet;
（3）静态分区导入方式，适合分区列distinct值少量的，比如该分区列只有2个值
1997-08-16 00:00:00
1999-08-17 00:00:00
导入方式如下，insert pt_table PARTITION (web_rec_start_date='1997-08-16 00:00:00') select指定目标列where web_rec_start_date='1997-08-16 00:00:00'
INSERT OVERWRITE TABLE pt_web_site PARTITION (web_rec_start_date='1997-08-16 00:00:00') select web_site_sk , web_site_id ,web_rec_end_date,web_name , web_open_date_sk , web_close_date_sk ,web_class,web_manager ,web_mkt_id,web_mkt_class,web_mkt_desc,web_market_manager ,web_company_id ,web_company_name , web_street_number ,web_street_name ,web_street_type ,web_suite_number ,web_city ,web_county ,web_state , web_zip , web_country ,web_gmt_offset ,web_tax_percentage FROM orc_web_site where web_rec_start_date='1997-08-16 00:00:00';
INSERT OVERWRITE TABLE pt_web_site PARTITION (web_rec_start_date='1999-08-17 00:00:00') select web_site_sk , web_site_id ,web_rec_end_date,web_name , web_open_date_sk , web_close_date_sk ,web_class,web_manager ,web_mkt_id,web_mkt_class,web_mkt_desc,web_market_manager ,web_company_id ,web_company_name , web_street_number ,web_street_name ,web_street_type ,web_suite_number ,web_city ,web_county ,web_state , web_zip , web_country ,web_gmt_offset ,web_tax_percentage FROM orc_web_site where web_rec_start_date='1999-08-17 00:00:00';
（4）通过动态分区方式导入
Hive上设置参数：set hive.exec.dynamic.partition.mode=nostrick;否则主分区不允许动态分区
INSERT OVERWRITE TABLE pt_web_site PARTITION (web_rec_start_date) select web_site_sk , web_site_id ,web_rec_end_date,web_name , web_open_date_sk , web_close_date_sk ,web_class,web_manager ,web_mkt_id,web_mkt_class,web_mkt_desc,web_market_manager ,web_company_id ,web_company_name , web_street_number ,web_street_name ,web_street_type ,web_suite_number ,web_city ,web_county ,web_state , web_zip , web_country ,web_gmt_offset ,web_tax_percentage,web_rec_start_date FROM orc_web_site ;
（5）将hive数据get到本地
hdfs dfs -get /user/hive/warehouse/pt_customer_address /mnt/data
- 点赞
- 收藏
- 关注作者
评论（0）