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