Sqoop导入替换mysql的特殊字符 [英] Sqoop Import replace special characters of mysql
问题描述
我有1000个表,每个表中有超过100000条记录的mysql。这些表格有300-500列。
一些表格的列名称中包含特殊字符,如。(点)和空格。
现在我想要执行sqoop导入,并在HDFS中创建一个配置单元表,如下所示:
sqoop import --connect $ {domain}:$ {port} / $(database)--username $ {username} --password $ {password} \
--table $(table) - m 1 --hive-import --hive-database $ {hivedatabase} --hive-table $(table)--create-hive-table \
--target-dir / user / hive / warehouse / $ {hivedatabase} .db / $(table)
在创建配置单元表后,查询表显示错误为
这个错误输出是一个示例输出。
编译语句时出错:FAILED:RuntimeException java.lang.RuntimeException:无法从[0:emp.id,1:emp.name,2:emp.salary,3:emp.dno]找到字段emp
我们如何取代。 (点)与_(下划线),而进行sqoop导入本身。我想动态地做到这一点。
使用sqoop import \ with - query 选项而不是--table和query使用替换功能。
即
sqoop import --connect $ {domain}:$ {port} / $(database) --username $ {username} --password $ {password} \
- query'选择col1,替换(col2,'。','_')作为列表中的列。
或者(不推荐)编写一个shell脚本,它可以找到并替换。在/ user / hive / warehouse / $ {hivedatabase} .db / $(table)中的_(Grep命令)
I have 1000 tables with more than 100000 records in each table in mysql. The tables have 300-500 columns.
Some of tables have columns with special characters like .(dot) and space in the column names.
Now I want to do sqoop import and create a hive table in HDFS in a single shot query like below
sqoop import --connect ${domain}:${port}/$(database) --username ${username} --password ${password}\
--table $(table) -m 1 --hive-import --hive-database ${hivedatabase} --hive-table $(table) --create-hive-table\
--target-dir /user/hive/warehouse/${hivedatabase}.db/$(table)
After this the hive table is created but when I query the table it shows error as
This error output is a sample output.
Error while compiling statement: FAILED: RuntimeException java.lang.RuntimeException: cannot find field emp from [0:emp.id, 1:emp.name, 2:emp.salary, 3:emp.dno]
How can we replace the .(dot) with _(underscore) while doing sqoop import itself. I would like to do this dynamically.
Use sqoop import \ with --query option rather than --table and in query use replace function .
ie
sqoop import --connect ${domain}:${port}/$(database) --username ${username} --password ${password}\ -- query 'Select col1 ,replace(col2 ,'.','_') as col from table.
Or (not recommended) write a shell script which can do find and replace "." to "_" (Grep command)at /user/hive/warehouse/${hivedatabase}.db/$(table)
这篇关于Sqoop导入替换mysql的特殊字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!