sqlserver导入hbase和hive

1.采用sqoop将sqlserver数据导入hbase

要先在hbase中将表建好(create ‘TicketInPark’,‘cf’)，再导入否则会报错

sqoop import --connect "jdbc:sqlserver://10.9.10.213:1433;database=CenterThemePark" \
--username sa --password 123456 --table TicketInPark \
--hbase-table TicketInPark \
--column-family cf \
--hbase-row-key Id

当报错“Generating splits for a textual index column allowed only in case of “-Dorg.apache.sqoop.splitter.allow_text_splitter=true” property passed as a parameter”
时，使用以下语句导入

sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true --connect "jdbc:sqlserver://10.9.10.213:1433;database=CenterThemePark" \
--username sa --password 123456 --table GroupTicket \
--hbase-table ticket_bases_data:GroupTicket \
--column-family cf \
--hbase-row-key Id

2.采用sqoop将sqlserver数据导入hive

sqlserver带sql查询的导入hive表

sqoop import --connect "jdbc:sqlserver://10.9.10.213:1433;database=CenterThemePark" \
--username sa --password 123456 \
--query 'select Id,ParkId,Barcode,TicketClassId,Qty,TerminalId,Remark,CreationTime, CAST(rv as BIGINT)rv from TicketInPark WITH (NOLOCK) where 1=1 and $CONDITIONS' \
--hive-import \
--target-dir /user/hive/warehouse/original_centerthemepark_18.db/ticketinpark12 \   （提前在hive中建好表的位置）
--hive-database original_centerthemepark_18 \
--hive-table ticketinpark12 \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '\\N' \
--as-parquetfile \
--split-by id \
-m 1 

sqlserver按表导入hive

sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true --connect "jdbc:sqlserver://10.9.10.213:1433;database=big_data" \
--username sa --password 123456 --table GroupTicket \
--hive-import \
--hive-database ods_ticketpark_data \
--fields-terminated-by '\t' \
--as-parquetfile \
--hive-table groupticket01 \
--split-by id \
-m 1 

sqlserver的全库导入hive

sqoop import-all-tables "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect "jdbc:sqlserver://10.9.10.213:1433;database=big_data" \
--username sa \
--password 123456 \
--hive-import \
--hive-overwrite \
--hive-database ods_ticketpark_data \
--fields-terminated-by '\t' \
--as-parquetfile 

导入脚本

#!/bin/bash
shoptable ="
Payment
SaleBill
SaleDetail
Store
"
for tav in $shoptable
do
	sqoop import --connect "jdbc:sqlserver://10.8.30.13:1433;database=CenterTheme_Dev_1819" \
	--username sa --password 1234 --table $tav \
	--hive-import \
	--hive-database ods_shop \
	--fields-terminated-by '\t' \
	--null-string '\\N' \
	--null-non-string '\\N' \
	--as-parquetfile \
	--split-by id \
	-m 1 
done