需求
使用datax将mysql中的数据拉取到hive的ods层
步骤
首先在mysql中确定好需要拉取的表user_extend，
然后对应在hive中创建好空表，等待拉取
这里对应创建的hive表格如下
CREATE TABLE snbap_ods.ods_user_extend (
user_id bigint ,
user_gender bigint ,
is_pregnant_woman tinyint ,
is_have_children tinyint ,
is_have_car tinyint ,
phone_brand string ,
phone_brand_level string ,
phone_cnt int ,
change_phone_cnt int ,
is_maja tinyint ,
majia_account_cnt int ,
loyal_model string ,
shopping_type_model string ,
weight int ,
height int
)
stored as textfile
location '/datax/mysql/snbap_ods/ods/ods_user_extend';
在datax的bin目录下创建json脚本 vi datax1.json
{
"job": {
"setting": {
"speed": {
"byte":1048576,
"channel":"4"
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "root",
"connection": [
{
"querySql": [
"select * from user_extend"
],
"jdbcUrl": [
"jdbc:mysql://localhost:3306/snbap_ods"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS":"hdfs://192.168.1.54:9000",
"fileType":"text",
"path":"/datax/mysql/snbap_ods/ods/ods_user_extend/",
"fileName":"user_extend",
"column": [
{"name":"user_id","type":"bigint"}
{"name":"user_gender","type":"bigint"}
{"name":"is_pregnant_woman","type":"tinyint"}
{"name":"is_have_children","type":"tinyint"}
{"name":"is_have_car","type":"tinyint"}
{"name":"phone_brand","type":"string"}
{"name":"phone_brand_level","type":"string"}
{"name":"phone_cnt","type":"int"}
{"name":"change_phone_cnt","type":"int"}
{"name":"is_maja","type":"tinyint"}
{"name":"majia_account_cnt","type":"int"}
{"name":"loyal_model","type":"string"}
{"name":"shopping_type_model","type":"string"}
{"name":"weight","type":"int"}
{"name":"height","type":"int"}
],
"writeMode":"append",
"fieldDelimiter":"\u0001",
"compress":"gzip"
}
}
}
]
}
}
在datax的bin目录下运行以上json脚本
python /opt/install/datax/bin/datax.py /opt/install/datax/bin/datax1.json
运行成功的结果
最后在hive里查看表中数据 select * from ods_user_extend limit 10;
0: jdbc:hive2://localhost:10000> select * from ods_user_extend limit 10;
INFO : Compiling command(queryId=root_20210709120808_33f5b7c6-df56-4474-8af9-329e0549b9f2): select * from ods_user_extend limit 10
INFO : Semantic Analysis Completed
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:ods_user_extend.user_id, type:bigint, comment:null), FieldSchema(name:ods_user_extend.user_gender, type:bigint, comment:null), FieldSchema(name:ods_user_extend.is_pregnant_woman, type:tinyint, comment:null), FieldSchema(name:ods_user_extend.is_have_children, type:tinyint, comment:null), FieldSchema(name:ods_user_extend.is_have_car, type:tinyint, comment:null), FieldSchema(name:ods_user_extend.phone_brand, type:string, comment:null), FieldSchema(name:ods_user_extend.phone_brand_level, type:string, comment:null), FieldSchema(name:ods_user_extend.phone_cnt, type:int, comment:null), FieldSchema(name:ods_user_extend.change_phone_cnt, type:int, comment:null), FieldSchema(name:ods_user_extend.is_maja, type:tinyint, comment:null), FieldSchema(name:ods_user_extend.majia_account_cnt, type:int, comment:null), FieldSchema(name:ods_user_extend.loyal_model, type:string, comment:null), FieldSchema(name:ods_user_extend.shopping_type_model, type:string, comment:null), FieldSchema(name:ods_user_extend.weight, type:int, comment:null), FieldSchema(name:ods_user_extend.height, type:int, comment:null)], properties:null)
INFO : Completed compiling command(queryId=root_20210709120808_33f5b7c6-df56-4474-8af9-329e0549b9f2); Time taken: 0.067 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing command(queryId=root_20210709120808_33f5b7c6-df56-4474-8af9-329e0549b9f2): select * from ods_user_extend limit 10
INFO : Completed executing command(queryId=root_20210709120808_33f5b7c6-df56-4474-8af9-329e0549b9f2); Time taken: 0.0 seconds
INFO : OK
+--------------------------+------------------------------+------------------------------------+-----------------------------------+------------------------------+------------------------------+------------------------------------+----------------------------+-----------------------------------+--------------------------+------------------------------------+------------------------------+--------------------------------------+-------------------------+-------------------------+--+
| ods_user_extend.user_id | ods_user_extend.user_gender | ods_user_extend.is_pregnant_woman | ods_user_extend.is_have_children | ods_user_extend.is_have_car | ods_user_extend.phone_brand | ods_user_extend.phone_brand_level | ods_user_extend.phone_cnt | ods_user_extend.change_phone_cnt | ods_user_extend.is_maja | ods_user_extend.majia_account_cnt | ods_user_extend.loyal_model | ods_user_extend.shopping_type_model | ods_user_extend.weight | ods_user_extend.height |
+--------------------------+------------------------------+------------------------------------+-----------------------------------+------------------------------+------------------------------+------------------------------------+----------------------------+-----------------------------------+--------------------------+------------------------------------+------------------------------+--------------------------------------+-------------------------+-------------------------+--+
| 1 | 1 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 262 | 178 |
| 2 | 1 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 226 | 209 |
| 3 | 1 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 214 | 133 |
| 4 | 1 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 268 | 212 |
| 5 | 1 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 81 | 158 |
| 6 | 0 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 173 | 212 |
| 7 | 0 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 85 | 141 |
| 8 | 1 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 146 | 199 |
| 9 | 0 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 143 | 314 |
| 10 | 0 | 0 | 0 | 0 | 华为 | 一级 | 1 | 3 | 0 | 0 | 高度 | 理智消费用户 | 208 | 209 |
+--------------------------+------------------------------+------------------------------------+-----------------------------------+------------------------------+------------------------------+------------------------------------+----------------------------+-----------------------------------+--------------------------+------------------------------------+------------------------------+--------------------------------------+-------------------------+-------------------------+--+
10 rows selected (0.115 seconds)
0: jdbc:hive2://localhost:10000>