1

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.

User12345
  • 5,180
  • 14
  • 58
  • 105

1 Answers1

2

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)

sandeep rawat
  • 4,797
  • 1
  • 18
  • 36
  • I tried the --Query Option but to replace in many column names using select query is where I am stuck – User12345 Jan 29 '17 at 06:55
  • Please excuse me for asking too may questions I am a bit new to Hadoop How do I write a dynamic query – User12345 Jan 29 '17 at 07:06
  • 2
    using INFORMATION_SCHEMA.COLUMNS you can get column name and column datatype and biased on column datatype for 'char','varchar' use Repace function . http://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names .... http://stackoverflow.com/questions/23178816/mysql-dynamic-query-in-stored-procedure – sandeep rawat Jan 29 '17 at 07:39