Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Select query on Hive partitioned table not working

Select query on Hive partitioned table not working

Explorer

Hello Team,

 

We have Hive external table created with partitioned. Below snap for partitions of table. 

 

show partitions db1.t1;

INFO : OK
+---------------------+--+
| partition |
+---------------------+--+
| bs1_dt=2017-06-23 |
| bs1_dt=2017-06-26 |
+---------------------+--+

 

When we run the below query from impala it works and shows the o/p in Hue and from command line.

 

Query:

select * from db1.t1;

 

But when we run the same query from hive it doesn't show any result in hue and get exited from beeline if running from beeline.

 

Kindly suggest?

 

Below query works in Hive beeline shell, how - kindly suggest?

select count(*) from db1.t1 where  bs1_dt=2017-06-23 and  bs1_dt=2017-06-26;

 

below query doesn't shows any result but gets queried, how - kindly suggest?

select c1 from db1.t1 where bs1=2017-06-23;

 

- Vijay M

 

4 REPLIES 4
Highlighted

Re: Select query on Hive partitioned table not working

Super Guru

@VijayM 

Try to run msck repair table

hive> msck repair table <db_name>.<table_name>;

 then run select and filter queries on the table.

 

For more details regards to msck repair table please refer to this link.

Re: Select query on Hive partitioned table not working

Guru
Hi Vijay,

Can you please share the output of running "select * from db1.t1;" in Hue and how it exited in beeline? This can help to clarify the behaviour.

What is the output of below query:
SELECT bs1_dt, COUNT(*) FROM db1.t1 GROUP BY bs1_dt;

Please also run below query in Impala, as it will show the partition location:
show partitions db1.t1;

Finally, output of "SHOW CREATE TABLE db1.t1" so that I can see the table definition.

Thanks
Eric

Re: Select query on Hive partitioned table not working

Explorer

@EricL ,

 

Thank You for the reply.

 

Below find o/p of query as requested.

 

When i ran the query from Hue Web UI under hive editor it throws error mentioned below.

Query: select * from db1.t1;

Error from Hue Web UI:

  • TSocket read 0 bytes (code THRIFTTRANSPORT): TTransportException('TSocket read 0 bytes',)

Error from Hue Error.log:

[12/Sep/2019 14:06:25 +0200] dbms DEBUG Query Server: {'server_name': 'beeswax', 'transport_mode': 'socket', 'server_host': 'xxxxx', 'server_port': 10000, 'auth_password_used': False, 'http_url': 'https://xxxxx:10001/cliservice', 'auth_username': 'hue', 'principal': 'hive/xxxx@yyyy'}
[12/Sep/2019 14:06:25 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.ExecuteStatement(args=(TExecuteStatementReq(confOverlay={}, sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret=aa4cb0e655407fc5:7343b16a27fe0183, guid=744cc0fec4cfbc68:f18115ce9a698e80)), runAsync=False, statement='USE `tdsvfactory`'),), kwargs={})
[12/Sep/2019 14:06:25 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.CloseOperation(args=(TCloseOperationReq(operationHandle=TOperationHandle(hasResultSet=False, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=1e4f07d444ff14ee:e7ecb1ed64fc3287, guid=d347eaef889d2a6a:7cb031b584d49a91))),), kwargs={})
[12/Sep/2019 14:06:25 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.ExecuteStatement(args=(TExecuteStatementReq(confOverlay={}, sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret=aa4cb0e655407fc5:7343b16a27fe0183, guid=744cc0fec4cfbc68:f18115ce9a698e80)), runAsync=True, statement='\nselect * from db1.t1'),), kwargs={})
[12/Sep/2019 14:06:26 +0200] access INFO zzzzzzz - "POST /notebook/api/execute/hive HTTP/1.1" returned in 1481ms
[12/Sep/2019 14:06:26 +0200] access INFO zzzzzzz  - "POST /desktop/log_analytics HTTP/1.1" -- PAGE: editor/showLongOperationWarning
[12/Sep/2019 14:06:26 +0200] access INFO zzzzzzz  - "POST /desktop/log_analytics HTTP/1.1" returned in 2ms
[12/Sep/2019 14:06:27 +0200] dbms DEBUG Query Server: {'server_name': 'beeswax', 'transport_mode': 'socket', 'server_host': 'xxxxxx', 'server_port': 10000, 'auth_password_used': False, 'http_url': 'https://xxxxx:10001/cliservice', 'auth_username': 'hue', 'principal': 'hive/axxxx@yyyy'}
[12/Sep/2019 14:06:27 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.GetOperationStatus(args=(TGetOperationStatusReq(operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=a94adfb0bfa6bcf3:b1b655a9dbdb1bab, guid=8d48b8ac1db4586e:d134e17b86c79fa5))),), kwargs={})
[12/Sep/2019 14:06:27 +0200] access INFO zzzzzzz - "POST /notebook/api/check_status HTTP/1.1" returned in 537ms
[12/Sep/2019 14:06:29 +0200] dbms DEBUG Query Server: {'server_name': 'beeswax', 'transport_mode': 'socket', 'server_host': 'xxxxx, 'server_port': 10000, 'auth_password_used': False, 'http_url': 'https://xxxxx:10001/cliservice', 'auth_username': 'hue', 'principal': 'hive/xxxxx@yyyyyyy'}
[12/Sep/2019 14:06:29 +0200] dbms DEBUG Query Server: {'server_name': 'beeswax', 'transport_mode': 'socket', 'server_host': 'xxxxx, 'server_port': 10000, 'auth_password_used': False, 'http_url': 'https://xxxxx:10001/cliservice', 'auth_username': 'hue', 'principal': 'hive/xxxxx@yyyyyyy'}
[12/Sep/2019 14:06:29 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.FetchResults(args=(TFetchResultsReq(fetchType=0, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=a94adfb0bfa6bcf3:b1b655a9dbdb1bab, guid=8d48b8ac1db4586e:d134e17b86c79fa5)), orientation=4, maxRows=100),), kwargs={})
[12/Sep/2019 14:06:29 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.FetchResults(args=(TFetchResultsReq(fetchType=1, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=a94adfb0bfa6bcf3:b1b655a9dbdb1bab, guid=8d48b8ac1db4586e:d134e17b86c79fa5)), orientation=4, maxRows=-1),), kwargs={})
[12/Sep/2019 14:06:29 +0200] access INFO zzzzzzz konstaal - "POST /notebook/api/get_logs HTTP/1.1" returned in 1096ms
[12/Sep/2019 14:06:29 +0200] thrift_util INFO Thrift exception; retrying: TSocket read 0 bytes
[12/Sep/2019 14:06:29 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.FetchResults(args=(TFetchResultsReq(fetchType=0, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=a94adfb0bfa6bcf3:b1b655a9dbdb1bab, guid=8d48b8ac1db4586e:d134e17b86c79fa5)), orientation=4, maxRows=100),), kwargs={})
[12/Sep/2019 14:06:29 +0200] thrift_util INFO Thrift exception; retrying: TSocket read 0 bytes
[12/Sep/2019 14:06:29 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.FetchResults(args=(TFetchResultsReq(fetchType=0, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=a94adfb0bfa6bcf3:b1b655a9dbdb1bab, guid=8d48b8ac1db4586e:d134e17b86c79fa5)), orientation=4, maxRows=100),), kwargs={})
[12/Sep/2019 14:06:29 +0200] thrift_util WARNING Out of retries for thrift call: FetchResults
[12/Sep/2019 14:06:29 +0200] thrift_util INFO Thrift saw a transport exception: TSocket read 0 bytes
[12/Sep/2019 14:06:29 +0200] decorators ERROR Error running fetch_result_data
Traceback (most recent call last):
File "/app/bds/parcels/CDH-5.15.1-1.cdh5.15.1.p0.4/lib/hue/desktop/libs/notebook/src/notebook/decorators.py", line 97, in decorator
return func(*args, **kwargs)
File "/app/bds/parcels/CDH-5.15.1-1.cdh5.15.1.p0.4/lib/hue/desktop/libs/notebook/src/notebook/api.py", line 227, in fetch_result_data
response['result'] = get_api(request, snippet).fetch_result(notebook, snippet, rows, start_over)
File "/app/bds/parcels/CDH-5.15.1-1.cdh5.15.1.p0.4/lib/hue/desktop/libs/notebook/src/notebook/connectors/hiveserver2.py", line 87, in decorator
raise QueryError(message)
QueryError
[12/Sep/2019 14:06:29 +0200] access INFO zzzzzzz konstaal - "POST /notebook/api/fetch_result_data HTTP/1.1" returned in 1393ms
[12/Sep/2019 14:06:30 +0200] access INFO zzzzzzz konstaal - "POST /desktop/log_analytics HTTP/1.1" -- PAGE: editor/showLongOperationWarning
[12/Sep/2019 14:06:30 +0200] access INFO zzzzzzz konstaal - "POST /desktop/log_analytics HTTP/1.1" returned in 2ms
[12/Sep/2019 14:06:30 +0200] dbms DEBUG Query Server: {'server_name': 'beeswax', 'transport_mode': 'socket', 'server_host': 'xxxxx, 'server_port': 10000, 'auth_password_used': False, 'http_url': 'https://xxxxx:10001/cliservice', 'auth_username': 'hue', 'principal': 'hive/xxxxx@yyyyyyy'}
[12/Sep/2019 14:06:30 +0200] thrift_util DEBUG Thrift call: <class 'TCLIService.TCLIService.Client'>.FetchResults(args=(TFetchResultsReq(fetchType=1, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret=a94adfb0bfa6bcf3:b1b655a9dbdb1bab, guid=8d48b8ac1db4586e:d134e17b86c79fa5)), orientation=4, maxRows=-1),), kwargs={})
[12/Sep/2019 14:06:30 +0200] access INFO zzzzzzz konstaal - "POST /notebook/api/fetch_result_size HTTP/1.1" returned in 531ms

 

 

When i ran below query from beeline i.e. commandline it gets exited from beeline with error mentioned below.

 

Query:  select * from tdb1.t1;

 

Error:  

15: jdbc:hive2://xxxxx> select * from db1.t1;
org.apache.thrift.transport.TTransportException
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.thrift.transport.TSaslTransport.readLength(TSaslTransport.java:376)
at org.apache.thrift.transport.TSaslTransport.readFrame(TSaslTransport.java:453)
at org.apache.thrift.transport.TSaslTransport.read(TSaslTransport.java:435)
at org.apache.thrift.transport.TSaslClientTransport.read(TSaslClientTransport.java:37)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.hadoop.hive.thrift.TFilterTransport.readAll(TFilterTransport.java:62)
at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429)
at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318)
at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77)
at org.apache.hive.service.cli.thrift.TCLIService$Client.recv_FetchResults(TCLIService.java:501)
at org.apache.hive.service.cli.thrift.TCLIService$Client.FetchResults(TCLIService.java:488)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hive.jdbc.HiveConnection$SynchronizedHandler.invoke(HiveConnection.java:1309)
at com.sun.proxy.$Proxy5.FetchResults(Unknown Source)
at org.apache.hive.jdbc.HiveQueryResultSet.next(HiveQueryResultSet.java:363)
at org.apache.hive.beeline.BufferedRows.<init>(BufferedRows.java:53)
at org.apache.hive.beeline.IncrementalRowsWithNormalization.<init>(IncrementalRowsWithNormalization.java:50)
at org.apache.hive.beeline.BeeLine.print(BeeLine.java:2110)
at org.apache.hive.beeline.Commands.executeInternal(Commands.java:1010)
at org.apache.hive.beeline.Commands.execute(Commands.java:1180)
at org.apache.hive.beeline.Commands.sql(Commands.java:1094)
at org.apache.hive.beeline.BeeLine.dispatch(BeeLine.java:1180)
at org.apache.hive.beeline.BeeLine.execute(BeeLine.java:1013)
at org.apache.hive.beeline.BeeLine.begin(BeeLine.java:922)
at org.apache.hive.beeline.BeeLine.mainWithInputRedirection(BeeLine.java:518)
at org.apache.hive.beeline.BeeLine.main(BeeLine.java:501)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:226)
at org.apache.hadoop.util.RunJar.main(RunJar.java:141)
Unknown HS2 problem when communicating with Thrift server.
Error: org.apache.thrift.transport.TTransportException: java.net.SocketException: Connection closed by remote host (state=08S01,code=0)

 

Below find o/p of query SELECT bs1_dt, COUNT(*) FROM db1.t1 GROUP BY bs1_dt;:

16: jdbc:hive2://a302-0553-7180.zur.swissbank> SELECT bsns_dt, COUNT(*) FROM tdsvfactory.model_us_stress GROUP BY bsns_dt;
+-----------------------------+--------+--+
| bsns_dt | _c1 |
+-----------------------------+--------+--+
| 2017-06-23 | 81 |
| 2017-06-26 | 21891 |
| __HIVE_DEFAULT_PARTITION__ | 21972 |
+-----------------------------+--------+--+
3 rows selected (26.423 seconds)

 

Ran Below query from impala and gives below o/p.

 

show partitions db1.t1;

 

bs1_dt#Rows#FilesSizeBytes CachedCache ReplicationFormatIncremental statsLocation
NULL-11396.90KBNOT CACHEDNOT CACHEDPARQUETFALSEhdfs://nameservice1/abc/xyz/efg/bs1_dt=__HIVE_DEFAULT_PARTITION__
6/23/2017-1126.11KBNOT CACHEDNOT CACHEDPARQUETFALSEhdfs://nameservice1/abc/xyz/efg/bs1_dt=2017-06-23
6/26/2017-113.09MBNOT CACHEDNOT CACHEDPARQUETFALSEhdfs://nameservice1/abc/xyz/efg/bs1_dt=2017-06-26
Total-133.51MB0B    

 

 

O/p of SHOW CREATE TABLE db1.t1

 

16: jdbc:hive2://xxxxx> show create table db1.t1;
+----------------------------------------------------+--+
| createtab_stmt |
+----------------------------------------------------+--+
| CREATE EXTERNAL TABLE `db1.t1`( |
| `delivery_id` int, |
| `as_of_date` timestamp, |
| `appl_id` string, |
| `sndr_id` string, |
| `rcvr_id` string, |
| `crdt_dbt` string, |
| `cur_cod` string, |
| `amnt` double, |
| `old_stlm_dt` timestamp, |
| `stlm_ts` timestamp, |
| `parent_flg` boolean, |
| `time_crtl_flg` boolean, |
| `cntl_bnk_flg` boolean, |
| `time_crtl_type` string, |
| `old_bsns_dt` timestamp, |
| `bsns_ts` timestamp, |
| `time_bckt` int, |
| `entity_gcrs_id` string, |
| `bank_own_flg` boolean, |
| `product_id` int, |
| `counterparty_cod` string, |
| `apollo_rfrc` string, |
| `date_from` timestamp, |
| PARTITIONED BY ( |
| `bs1_dt` string) |
| ROW FORMAT SERDE |
| 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' |
| STORED AS INPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' |
| OUTPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' |
| LOCATION |
| 'hdfs://nameservice1/abc/xyz/efg' |
| TBLPROPERTIES ( |
| 'spark.sql.create.version'='2.2 or prior', |
| 'spark.sql.sources.schema.numPartCols'='1', |
| 'spark.sql.sources.schema.numParts'='2', |
| 'spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fields\":[{\"name\":\"delivery_id\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}},{\"name\":\"as_of_date\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"appl_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sndr_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"rcvr_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"crdt_dbt\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cur_cod\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"amnt\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"old_stlm_dt\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_ts\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"clrg_chnl_cod\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"rfrc_no\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sndr_to_rcvr_inf\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"bnfr_acct\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"mssg_type\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"inbd_lnk_flg\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"fme\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"prty_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"vldn\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"clrg_acct\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"srce_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sndr_entt_name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"rcvr_entt_name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sndr_entt_cat\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"rcvr_entt_cat\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sndr_entt_gcrs_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"rcvr_entt_gcrs_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"group_flg\",\"type\":\"boolean\",\"nullable\":true,\"metadata\":{}},{\"name\":\"parent_flg\",\"type\":\"boolean\",\"nullable\":true,\"metadata\":{}},{\"name\":\"time_crtl_flg\",\"type\":\"boolean\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cntl_bnk_flg\",\"type\":\"boolean\",\"nullable\":true,\"metadata\":{}},{\"name\":\"time_crtl_type\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"old_bsns_dt\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"bsns_ts\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"time_bckt\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_dt\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_ts\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_time_zone\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_time_zone\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_utc_dt\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_utc_ts\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_dt\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"entity_flg\",\"type\":\"boolean\",\"nullable\":true,\"metadata\":{}},{\"name\":\"entity_gcrs_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"bank_own_flg\",\"type\":\"boolean\",\"nullable\":true,\"metadata\":{}},{\"name\":\"product_id\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}},{\"name\":\"counterparty_cod\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"apollo_rfrc\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"date_from\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"date_to\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"ilmdl_hash\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"amnt_signed\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_ts_stress_1hr\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_ts_excludefmu_stress_1hr\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_ts_excludefmu50mm_stress_1hr\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_ts_stress_2hr\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}}', |
| 'spark.sql.sources.schema.part.1'=',{\"name\":\"stlm_local_ts_excludefmu_stress_2hr\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"stlm_local_ts_excludefmu50mm_stress_2hr\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts_stress_1hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts_excludefmu_stress_1hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts_excludefmu50mm_stress_1hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts_stress_2hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts_excludefmu_stress_2hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"cum_sum_stlm_local_ts_excludefmu50mm_stress_2hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts_stress_1hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts_stress_1hr\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts_excludefmu_stress_1hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts_excludefmu_stress_1hr\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts_excludefmu50mm_stress_1hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts_excludefmu50mm_stress_1hr\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts_stress_2hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts_stress_2hr\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts_excludefmu_stress_2hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts_excludefmu_stress_2hr\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_stlm_local_ts_excludefmu50mm_stress_2hr\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"trough_ts_stlm_local_ts_excludefmu50mm_stress_2hr\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"process_dt\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}},{\"name\":\"bsns_dt\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}', |
| 'spark.sql.sources.schema.partCol.0'='bs1_dt', |
| 'transient_lastDdlTime'='123464577') |
+----------------------------------------------------+--+

 

 

- Vijay M

 

 

Re: Select query on Hive partitioned table not working

Guru
Hi Vijay,

Thanks for the detailed output.

Looks like both Hue and Beeline have the same issue, that both of they were not able to retrieve results from HS2. A few more questions:

1. how many HS2 hosts do you have?
2. Can you check the HS2 log on the host that Hue and Beeline connects to and see what error you can get from there? It might tell more story on why it failed
3. Can you also try below queries:

select * from tdb1.t1 where bs1_dt=2017-06-23;
select * from tdb1.t1 where bs1_dt=2017-06-26;

And see which partition might be causing the issue, or is it both?

Thanks
Eric