[Sqoop] sqoop import로 Hive table 만들시 base 디렉터리 미생성 이슈

 

버전정보

hive 3.1.3

Sqoop 1.4.7

 

 

sqoop import로 Hive테이블을 만드는데 당연히 Managed table이므로 타 DB에서 데이터를 가져올 때 기본적으로 Base디렉터리가 생겨야한다.

 

하지만 데이터만 덩그라니 있고, base디렉터리가 생기지 않았다.

그러다보니 select쿼리시 결과 출력이 되지 않았다.

 

sqoop import를 디버그 모드로 실행해보니 원인을 찾을 수 있었다.

 

-처음 명령어

sqoop import \ --connect 'jdbc:mysql://~~~~~:3306/information_schema' \ --username 'test_user' \ --password 'test_user!01' \ --query "select * from TABLES where \$CONDITIONS" \ --m 1 \ --target-dir /user/hive/warehouse/test.db/information_schema_tmp \ --delete-target-dir \ --hive-import \ --hive-table test.information_schema_tmp \ --hive-overwrite \ --hive-drop-import-delims \ --driver cohttp://m.mysql.cj.jdbc.Driver \ --hs2-user mytest \ --hs2-password 'mytest!01' \ --hs2-url 'jdbc:hive2://hiveserver2:10000' \ --verbose

 

25/09/12 03:31:37 DEBUG hive.HiveServer2Client: Executing command: LOAD DATA INPATH 'hdfs://nameservice1/user/hive/warehouse/test.db/information_schema_tmp' OVERWRITE INTO TABLE `test.information_schema_tmp3` 25/09/12 03:31:38 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Error executing Hive import. java.lang.RuntimeException: Error executing Hive import.         at org.apache.sqoop.hive.HiveServer2Client.executeHiveImport(HiveServer2Client.java:85)         at org.apache.sqoop.hive.HiveServer2Client.importTable(HiveServer2Client.java:63)         at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:549)         at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:633)         at org.apache.sqoop.Sqoop.run(Sqoop.java:146)         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:82)         at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:182)         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:233)         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:242)         at org.apache.sqoop.Sqoop.main(Sqoop.java:251) Caused by: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask. Unable to move source hdfs://nameservice1/user/hive/warehouse/test.db/information_schema_tmp to destination hdfs://nameservice1/user/hive/warehouse/test.db/information_schema_tmp         at org.apache.hive.jdbc.HiveStatement.waitForOperationToComplete(HiveStatement.java:401)         at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:266)         at org.apache.hive.jdbc.HivePreparedStatement.execute(HivePreparedStatement.java:101)         at org.apache.sqoop.hive.HiveServer2Client.executeCommands(HiveServer2Client.java:96)         at org.apache.sqoop.hive.HiveServer2Client.executeHiveImport(HiveServer2Client.java:83)         ... 9 more

 

디버그모드로 확인해보니 sqoop import마지막에 알아서 hive query인 LOAD DATA INPATH를 하는 것을 알 수 있다.

 

이때 sqoop import의 --target-dir이 테이블 경로와 같아서 에러가 발생했다.

 

따라서 아래와 같이 --target-dir 경로를 임시 경로로 바꿔주니 성공했다.

sqoop import \ --connect 'jdbc:mysql://~~~~~:3306/information_schema' \ --username 'test_user' \ --password 'test_user!01' \ --query "select * from TABLES where \$CONDITIONS" \ --m 1 \ --target-dir /tmp/information_schema_tmp \ --delete-target-dir \ --hive-import \ --hive-table test.information_schema_tmp \ --hive-overwrite \ --hive-drop-import-delims \ --driver cohttp://m.mysql.cj.jdbc.Driver \ --hs2-user mytest \ --hs2-password 'mytest!01' \ --hs2-url 'jdbc:hive2://hiveserver2:10000' \ --verbose

 

$ hdfs dfs -ls /user/hive/warehouse/test.db/information_schema_tmp Found 1 items drwxrwxrwx+  - h1116296 hdfs          0 2025-09-12 03:36 /user/hive/warehouse/test.db/information_schema_tmp/base_0000001

 

hive select쿼리도 잘 동작한다.