Ingesting dump db (.sql) into hdfs

[#1]

Hi All,

I need to ingest data from database dump (.sql) file. May I know, the best method how to load the data into hive table? Below are some info the file we need to ingest. 

<database name>_<table name>_yyyymmdd.sql

• <database name> is the name of the database exported.
• ‘yyyymmdd’ is the date the file was created.

Regards,

Khairul

[#2]

@keroz, Welcome to our community! To help you get the best possible answer, I have tagged our experts @RangaReddy @mszurap @Gopinath who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

[#3]

In general, a dump.sql file from an RDBMS can be read using the respective RDBMS command line utility. For instance, the mysql dump.sql file can be read using the mysql utility. To read MySQL data from Hive, you have two options: you can either utilize the HIVE JDBC Handler or import the RDBMS data into Hive tables using Sqoop.

Ref - 
https://cwiki.apache.org/confluence/display/Hive/JDBC+Storage+Handler
https://docs.cloudera.com/cdp-private-cloud-base/7.1.8/migrating-data-into-hive/topics/hive_moving_d...


JdbcStorageHandler reads the data from the JDBC data source 
Sqoop import command that imports data from diverse data source. 

[#4]

Hi ggangadharan,

Thanks for you feedback. Maybe I need to explain more detail on my case above. Both solution you give more to sqoop/import the data using the JDBC. My current situation, the source system not give the permission to access their db. They will export the file and save as .sql file. I need to download the file and load into hive table.