Search code examples
hadoophdfshiveqlcreate-tablehiveddl

Failed make hive table on desired path and insert the values

I want to make table in hive containing of only 1 column and 2 values: 'Y' and 'N'

I already try this:

create external table if not exists tx_test_table  (FLAG string) 
row format delimited fields terminated by ','
stored as textfile location "/user/hdd/data/";

My question is : why it locate at default table? how to make it through the path I desire?

When I make query from the table I jut make, it failed to show the field (using select * from )

Bad status for request TFetchResultsReq(fetchType=0,
operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None,
operationType=0, 
operationId=THandleIdentifier(secret='pE\xff\xfdu\xf6B\xd4\xb3\xb7\x1c\xdd\x16\x95\xb85', 
guid="\n\x05\x16\xe7'\xe4G \xb6R\xe06\x0b\xb9\x04\x87")), 
orientation=4, maxRows=100): 
TFetchResultsResp(status=TStatus(errorCode=0,
 errorMessage='java.io.IOException: java.io.IOException: Not a file:
 hdfs://nameservice1/user/hdd/data/AC22', sqlState=None,
 infoMessages=['*org.apache.hive.service.cli.HiveSQLException:java.io.IOException:
 java.io.IOException: Not a file: hdfs://nameservice1/user/hdd/data/AC22:14:13', 
'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:496', 
'org.apache.hive.service.cli.operation.OperationManager:getOperationNextRowSet:OperationManager.java:297', 
'org.apache.hive.service.cli.session.HiveSessionImpl:fetchResults:HiveSessionImpl.java:869', 'org.apache.hive.service.cli.CLIService:fetchResults:CLIService.java:507', 
'org.apache.hive.service.cli.thrift.ThriftCLIService:FetchResults:ThriftCLIService.java:708', 
'org.apache.hive.service.rpc.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1717', 
'org.apache.hive.service.rpc.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1702', 
'org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39', 
'org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39', 'org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:605', 
'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286', 
'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1149', 
'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624', 'java.lang.Thread:run:Thread.java:748', 
'*java.io.IOException:java.io.IOException: Not a file: hdfs://nameservice1/user/hdd/data/AC22:18:4', 
'org.apache.hadoop.hive.ql.exec.FetchOperator:getNextRow:FetchOperator.java:521'
, 'org.apache.hadoop.hive.ql.exec.FetchOperator:pushRow:FetchOperator.java:428', 
'org.apache.hadoop.hive.ql.exec.FetchTask:fetch:FetchTask.java:146', 
'org.apache.hadoop.hive.ql.Driver:getResults:Driver.java:2227', 
'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:491', 
'*java.io.IOException:Not a file: hdfs://nameservice1/user/hdd/data/AC22:21:3', 
'org.apache.hadoop.mapred.FileInputFormat:getSplits:FileInputFormat.java:329', 
'org.apache.hadoop.hive.ql.exec.FetchOperator:getNextSplits:FetchOperator.java:372', 
'org.apache.hadoop.hive.ql.exec.FetchOperator:getRecordReader:FetchOperator.java:304', 
'org.apache.hadoop.hive.ql.exec.FetchOperator:getNextRow:FetchOperator.java:459'], statusCode=3),
 results=None, hasMoreRows=None)
Solution

  • Each table in HDFS has it's own location. And location you specified for your table seems used as common location where other table folders are located.

    According to the exception: java.io.IOException:Not a file: hdfs://nameservice1/user/hdd/data/AC22:21:3', at least one folder (not a file) was found in the /user/hdd/data/ location. I guess it belongs to some other table.

    You should specify table location where will be stored only files which belong to this table, not the common data warehouse location, in which other table locations are.

    Usually table location is named as table name: /user/hdd/data/tx_test_table

    Fixed create table sentence:

    create external table if not exists tx_test_table  (FLAG string) 
row format delimited fields terminated by ','
stored as textfile location "/user/hdd/data/tx_test_table";

    Now table will have it's own location which will contain it's files, not mixed with other table folders or files.

    You can put files into /user/hdd/data/tx_test_table location or load data into the table using INSERT, files will be created in the location.