1

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)
thecardcaptor
  • 131
  • 7

1 Answers1

1

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.

thecardcaptor
  • 131
  • 7
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • it works! thank you.. how about if I want to make some temporary table with FLAG column with only Y and N value? – thecardcaptor Dec 22 '20 at 13:42
  • @thecardcaptor temp tables are usually managed and being deleted automatically when session ends and this is why you do not need to bother about it's location, let it be default one. Hive will create it in user temp. why do you need table with sinle col and only Y, N ??? – leftjoin Dec 22 '20 at 13:48
  • 1
    @thecardcaptor Answered your question, please check. You do not need temp table for that, also you do not need SELECT + UNION ALL, do not need dual. stack() works faster – leftjoin Dec 22 '20 at 13:58
  • would you mind if we have a chat for about 5 mins? – thecardcaptor Dec 22 '20 at 14:14
  • @thecardcaptor okay – leftjoin Dec 22 '20 at 14:19
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226296/discussion-between-leftjoin-and-thecardcaptor). – leftjoin Dec 22 '20 at 14:19