sqoop1实战 数据导出

Sqoop数据导入到Hive

🧙 Questions

• Hdfs导出到Mysql

☄️ Ideas

Note:
Sqoop数据导出，是从hdfs中同步到关系型数据库，目标表必须提前创建

Note:
尝试用使用特殊字符作为分割符 \001 或者 @ 或者 ^A

参数说明(export-args 用--)

Argument	Description
verbose	打印更多日志
connect	数据库连接 jdbcUrl
driver	数据库驱动 className
username	数据库连接用户
password	数据库连接密码
columns	目标表中对应字段
table	目标表表名
export-dir	hdfs的数据来源
num-mappers	任务并发数
input-null-string	string的字段类型值为null如何处理
input-null-non-string	不是string的字段类型值为null如何处理
batch	开启批量同步
input-fields-terminated-by	字段分割符
input-lines-terminated-by	行分割符
validate	开启校验数据量

Hdfs导出到Mysql

sqoop export \
    -Dorg.apache.sqoop.export.text.dump_data_on_error=true \
    --verbose \
    --validate \
    --validator org.apache.sqoop.validation.RowCountValidator \
    --validation-threshold org.apache.sqoop.validation.AbsoluteValidationThreshold \
    --validation-failurehandler org.apache.sqoop.validation.AbortOnFailureHandler \
    --batch \
    --driver com.mysql.cj.jdbc.Driver \
    --connect jdbc:mysql://192.168.16.74:3306/ispong_assets \
    --username ispong \
    --password define123 \
    --table define_part \
    --export-dir hdfs://192.168.16.75:8020/user/hive/warehouse/cdh_dev.db/define_part \
    --input-fields-terminated-by '\001' \
    --input-lines-terminated-by '\n' \
    --num-mappers 4 \
    --input-null-string '\\N' \
    --input-null-non-string '\\N'

sqoop export --verbose --connect 'jdbc:mysql://192.168.19.90:3306/bigdata?zeroDateTimeBehavior=convertToNull' --username root --password q#IwJ,c?gB/F --table dim_rtm_vehicle --export-dir /user/hive/warehouse/test_car.db/dim_rtm_vehicle --num-mappers 1 --columns id,vin,imei,activate_state,soft_version,plate,factory_id,model_name,brand_name,office_id,office_name,city,province,nation,latest_register,create_date,create_by,update_date,update_by,del_flag,own_account_id,pay_account_id,servicestatus,producedate,vehiclestatus,service_open_time,service_pause_time,service_recovery_time,service_certification_time,historytrajectory,isshowcar,ecall_status,mp5online,owner_change_date,bolt_date,mark,tserve_term,cp_term,monitor_status,credit_lock,park_photo_name,park_lon,park_lat --input-null-string 'Null' --input-null-non-string 'Null' --input-fields-terminated-by '\001'

🔗 Links

• sqoop website
• sqoop export docs