Select query on Hive partitioned table not working

[#1]

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

[#2]

@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.

[#3]

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

[#4]

@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	#Files	Size	Bytes Cached	Cache Replication	Format	Incremental stats	Location
NULL	-1	1	396.90KB	NOT CACHED	NOT CACHED	PARQUET	FALSE	hdfs://nameservice1/abc/xyz/efg/bs1_dt=__HIVE_DEFAULT_PARTITION__
6/23/2017	-1	1	26.11KB	NOT CACHED	NOT CACHED	PARQUET	FALSE	hdfs://nameservice1/abc/xyz/efg/bs1_dt=2017-06-23
6/26/2017	-1	1	3.09MB	NOT CACHED	NOT CACHED	PARQUET	FALSE	hdfs://nameservice1/abc/xyz/efg/bs1_dt=2017-06-26
Total	-1	3	3.51MB	0B

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

[#5]

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

[#6]

@EricL

Sorry for delay in response from my end. below find response to your query. 

1. how many HS2 hosts do you have? - Two HS2 installed
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

-- Nothing gets reported in both HS2 logs when running the query select * from db1.t1; from Hue Web UI.

-- Nothing gets reported in both HS2 logs when running the query select * from db1.t1; from beeline but beeline throws below error

0: jdbc:hive2://x.x.x.x> 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.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.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.

-- Below is the log/Error from Hue Web UI while running query select * from db1.t1;

Bad status for request TFetchResultsReq(fetchType=0, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret="3\x96k'l>Ns\x82\xa2\xf2\x91\x9e\xe8_#", guid='\xeb\xd4\xc7\xab+\xe1CU\x95\x956n7V\x8f\x7f')), orientation=4, maxRows=100): TFetchResultsResp(status=TStatus(errorCode=0, errorMessage='Invalid OperationHandle: OperationHandle [opType=EXECUTE_STATEMENT, getHandleIdentifier()=ebd4c7ab-2be1-4355-9595-366e37568f7f]', sqlState=None, infoMessages=['*org.apache.hive.service.cli.HiveSQLException:Invalid OperationHandle: OperationHandle [opType=EXECUTE_STATEMENT, getHandleIdentifier()=ebd4c7ab-2be1-4355-9595-366e37568f7f]:12:11', 'org.apache.hive.service.cli.operation.OperationManager:getOperation:OperationManager.java:177', 'org.apache.hive.service.cli.CLIService:fetchResults:CLIService.java:462', 'org.apache.hive.service.cli.thrift.ThriftCLIService:FetchResults:ThriftCLIService.java:694', 'org.apache.hive.service.cli.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1553', 'org.apache.hive.service.cli.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1538', '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:747', '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'], statusCode=3), results=None, hasMoreRows=None)

-- Below is the log/Error from Hue runrpcserver.log while running query select * from db1.t1;

[19/Sep/2019 08:11:55 +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 81, 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/connectors/hiveserver2.py", line 296, in fetch_result
raise QueryError(ex)
QueryError
[19/Sep/2019 08:11:55 +0200] access INFO 10.87.64.203 konstaal - "POST /notebook/api/fetch_result_data HTTP/1.1" returned in 1001ms

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?

-- Both above queries gets successfully ran but it shows only Schema information in result and no values. Whereas in Impala running above queries gives schema and values both.

-- Hue Web Ui shows result as "Done. 0 results."

- Beeline shows no values for all Schema defined in table.

- Vijay M

[#7]

@EricL,

Your response will help much.

- Vijay M

[#8]

I am a bit running out of ideas, but a couple of more questions:

1. what version of CDH are you using? I can't remember if I have asked

2. can you try to remove below directory in HDFS:
hdfs://nameservice1/abc/xyz/efg/bs1_dt=__HIVE_DEFAULT_PARTITION__

and also drop the partition on the table against this path, just to make sure that it is clean

and try again in in beeline with your problematic query?

I remember this might cause issue sometimes, but can't remember which version though, need a bit more digging.

Cheers

[#9]

@EricL 

We have CDH 5.15.1.

As suggested i have deleted  the partition  __HIVE_DEFAULT_PARTITION__ and deleted from HDFS too.

Then ran select * on db1.t1 where bs1_dt=2017-06-23;   

Issue remain same, it only shows Schema information but no values for schema.

Kindly suggest?

- Vijay M

[#10]

Hi Vijay,

Sorry, haven't able to nail down the cause yet, but can you collect EXPLAIN EXTENDED of the query and share the output as attachment to the post?

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

I would like to check how HS2 does the query plan and see if there is any clue.

Cheers
Eric