Remove Spaces/Special Characters in Column Name while sqoop from Teradata to cloudera hive

Question

i'm able to sqoop the Teradata table into Hive Cloudera sucessfully, But Teradata table has column name with Space and special characters like "Material ID", "Active/InActive" like this, but in hive this not standard and facing lot of issues while querying.

How to remove spaces and special characters in column name while inserting?

i'm using below sqoop command,

sqoop import --connect "jdbc:teradata://ABCD/database=db1,logmech=LDAP" \
--username **** \
--password **** \
--hive-import \
--hive-overwrite \ 
--hive-database abc_stg \ 
--hive-table product_dim \
--query "select * from product_dim where \$CONDITIONS" \
--target-dir /dev/edl/md/addy/str/abc_stg/product_dim \
--fields-terminated-by '|' -m 1 --delete-target-dir

FYI In Teradata the object name qualifiers are `"` (e.g.`"Material ID"`). In Hive its `\`` (e.g. `\`Material ID\``) — David דודו Markovitz, Mar 10 '17 at 13:54
If it aren't too many columns you can use `select column_name as simple_name,... from product_dim where ...`. This will solve your issue but not in a very clean way. — spijs, Mar 10 '17 at 14:49
@spijs, columns are many and I need to do it for Manu tables — Haneep CR, Mar 11 '17 at 20:29
@Dudu Markovitz, thanks for your comment, we don't have any way so I can remove space in column names — Haneep CR, Mar 11 '17 at 20:31
@HaneepCR What you still could do is create a script which will read all column names, replace the special characters and then modify and run a sqoop command containing `column_name as simple_name` for all columns. It is a workaround but it should work and scale. — spijs, Mar 12 '17 at 11:57
@DuduMarkovitz, Sqoop has any inbuild function which removes space and special character in column names while creating table in hive? — Haneep CR, Mar 12 '17 at 18:35
http://stackoverflow.com/questions/41902381/sqoop-import-replace-special-characters-of-mysql — spijs, Mar 13 '17 at 07:28

Remove Spaces/Special Characters in Column Name while sqoop from Teradata to cloudera hive

0 Answers0