Cloudera Community
ggangadharan
user rank
user rank
Super Collaborator

Re: Ozzie Workflow for CTE and Insert Statement

by Super Collaborator Super Collaborator in Support Questions
‎10-03-2023 02:15 AM
‎10-03-2023 02:15 AM
It seems that the query involves dynamic partitioning, but the dynamic partition column is not included in either the select statement or the Common Table Expression (CTE). Please add the dynamic partition column 'date' to the select statement and validate it in Beeline. ... View more

Re: Insert data from spark data frame to hive orc ...

by Super Collaborator Super Collaborator in Support Questions
‎10-03-2023 02:07 AM
‎10-03-2023 02:07 AM
It appears that you're currently following a two-step process: writing data to a Parquet table and then using that Parquet table to write to an ORC table. You can streamline this by directly writing the data into the ORC format table, eliminating the need to write the same data to a Parquet table before reading it.  Ref -  https://spark.apache.org/docs/2.4.0/sql-data-sources-hive-tables.html https://docs.cloudera.com/cdp-private-cloud-base/7.1.9/developing-spark-applications/topics/spark-loading-orc-data-predicate-push-down.html   ... View more

Re: Question PIG and HIVE Code

by Super Collaborator Super Collaborator in Support Questions
‎10-03-2023 01:58 AM
‎10-03-2023 01:58 AM
You can use the following HQL (SQL) query to find the sum of new cases for each continent and identify the country with the highest number of cases in each continent along with the corresponding case count:     WITH ContinentSum AS ( SELECT continent, SUM(new_cases) AS total_new_cases FROM sample_table_test GROUP BY continent ), CountryMaxCases AS ( SELECT continent, location AS country, MAX(total_case) AS max_cases FROM sample_table_test GROUP BY continent, location ) SELECT cs.continent, cs.total_new_cases, cm.country, cm.max_cases FROM ContinentSum cs JOIN CountryMaxCases cm ON cs.continent = cm.continent AND cs.total_new_cases = cm.max_cases;     This query first calculates the sum of new cases for each continent in the ContinentSum CTE (Common Table Expression). Then, it finds the country with the highest total cases in each continent using the CountryMaxCases CTE. Finally, it joins the results from both CTEs to provide the desired output. Sample resultset for the shared data.     +---------------+---------------------+--------------+---------------+ | cs.continent | cs.total_new_cases | cm.country | cm.max_cases | +---------------+---------------------+--------------+---------------+ | Asia | 25.0 | Afghanistan | 25.0 | +---------------+---------------------+--------------+---------------+     ... View more

Re: Why does a NULL value appear when I count to c...

by Super Collaborator Super Collaborator in Support Questions
‎09-30-2023 11:39 AM
‎09-30-2023 11:39 AM
To pinpoint the root cause, kindly provide a few samples of data ... View more

Re: Hortonworks Hive 35 Error

by Super Collaborator Super Collaborator in Support Questions
‎09-30-2023 11:36 AM
‎09-30-2023 11:36 AM
It seems that the failure occurred within the Tez job's child tasks. To identify the root cause, please share the YARN logs of the failed application ... View more

Re: Is it possible to disable column level securit...

by Super Collaborator Super Collaborator in Support Questions
‎09-29-2023 05:27 AM
‎09-29-2023 05:27 AM
To gain a better understanding of the issue, kindly provide the HS2 jstacks at 30-second intervals until the query completes ... View more

Re: CDP - Facing issues with Hive

by Super Collaborator Super Collaborator in Support Questions
‎09-29-2023 05:25 AM
‎09-29-2023 05:25 AM
The stack traces for Error 1 and Error 3 are incomplete. To gain a better understanding of the issue, please provide the complete stack traces. Sharing the complete appLogs will provide a comprehensive view of the situation Regarding error 2, it appears that the job is attempting to create over 2000 dynamic partitions on a single node, which is an unusual behavior. Please review the partition column values for correctness. If everything appears to be in order, you can consider adjusting the following configurations: hive.exec.max.dynamic.partitions hive.exec.max.dynamic.partitions.pernode ... View more

Re: Create Hive Metastore database tables has fail...

by Super Collaborator Super Collaborator in Support Questions
‎09-29-2023 05:17 AM
‎09-29-2023 05:17 AM
It appears that the Hive Metastore (HMS) is unable to establish a connection with the BackendDB, possibly due to an incorrect hostname or BackendDB configuration within the Hive service. Please validate the BackendDB configurations and attempt to start the service again. Exception in thread "main" java.lang.RuntimeException: org.postgresql.util.PSQLException: The connection attempt failed. at com.cloudera.cmf.service.hive.HiveMetastoreDbUtil.countTables(HiveMetastoreDbUtil.java:203) at com.cloudera.cmf.service.hive.HiveMetastoreDbUtil.printTableCount(HiveMetastoreDbUtil.java:284) at com.cloudera.cmf.service.hive.HiveMetastoreDbUtil.main(HiveMetastoreDbUtil.java:354) Caused by: org.postgresql.util.PSQLException: The connection attempt failed. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:297) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:217) at org.postgresql.Driver.makeConnection(Driver.java:458) at org.postgresql.Driver.connect(Driver.java:260) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at com.cloudera.enterprise.dbutil.SqlRunner.open(SqlRunner.java:193) at com.cloudera.enterprise.dbutil.SqlRunner.getDatabaseName(SqlRunner.java:264) at com.cloudera.cmf.service.hive.HiveMetastoreDbUtil.countTables(HiveMetastoreDbUtil.java:197) ... 2 more Caused by: java.net.SocketTimeoutException: connect timed out at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) at java.net.Socket.connect(Socket.java:607) at org.postgresql.core.PGStream.<init>(PGStream.java:81) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:93) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:197) ... View more

Re: How to load existing partitoned parquet data i...

by Super Collaborator Super Collaborator in Support Questions
‎09-27-2023 07:43 AM
‎09-27-2023 07:43 AM
if the partition data exists like below: <s3:bucket>/<some_location>/<part_column>=<part_value>/<filename> you can create a external table by specifiying above location and run 'msck repair table <table_name> sync partitions' to sync partitions. validate the data by running some sample select statements. Once it's done you can create new external table with another bucket and run insert statement with dynamic partition. Ref - https://cwiki.apache.org/confluence/display/hive/dynamicpartitions ... View more

Re: Hive on tez cannot execute custom hook program...

by Super Collaborator Super Collaborator in Support Questions
‎09-27-2023 07:12 AM
1 Kudo
‎09-27-2023 07:12 AM
1 Kudo
In CDP, when the HiveProtoLoggingHook is configured, query information is automatically captured and stored in the 'query_data' folder, which is typically located where 'hive.hook.proto.base-directory' is set. These details are saved as protobuf files, and in Hive, you can utilize the ProtobufMessageSerDe to access them. To read this captured data, you can create a table as shown below. CREATE EXTERNAL TABLE `query_data`( `eventtype` string COMMENT 'from deserializer', `hivequeryid` string COMMENT 'from deserializer', `timestamp` bigint COMMENT 'from deserializer', `executionmode` string COMMENT 'from deserializer', `requestuser` string COMMENT 'from deserializer', `queue` string COMMENT 'from deserializer', `user` string COMMENT 'from deserializer', `operationid` string COMMENT 'from deserializer', `tableswritten` array<string> COMMENT 'from deserializer', `tablesread` array<string> COMMENT 'from deserializer', `otherinfo` map<string,string> COMMENT 'from deserializer') PARTITIONED BY ( `date` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.protobuf.ProtobufMessageSerDe' WITH SERDEPROPERTIES ( 'proto.class'='org.apache.hadoop.hive.ql.hooks.proto.HiveHookEvents$HiveHookEventProto', 'proto.maptypes'='org.apache.hadoop.hive.ql.hooks.proto.MapFieldEntry') STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.protobuf.ProtobufMessageInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveNullValueSequenceFileOutputFormat' LOCATION '<query_datalocation>' TBLPROPERTIES ( 'bucketing_version'='2', 'proto.class'='org.apache.hadoop.hive.ql.hooks.proto.HiveHookEvents$HiveHookEventProto') After creating the table, execute 'msck repair query_data sync partitions' to synchronize the partitions, and then you can retrieve and analyze the data using Beeline. ... View more
Contact Me
Online
Offline
Last Visited
‎10-17-2024 05:48 AM
Community Statistics
Member Since ‎09-16-2021 02:45 AM
Last Visited ‎10-17-2024 05:48 AM
Posts 292
Kudos received 39