Member since
10-28-2020
541
Posts
44
Kudos Received
39
Solutions
My Accepted Solutions
Title | Views | Posted |
---|---|---|
3301 | 07-23-2024 11:49 PM | |
448 | 05-28-2024 11:06 AM | |
799 | 05-05-2024 01:27 PM | |
473 | 05-05-2024 01:09 PM | |
540 | 03-28-2024 09:51 AM |
09-24-2024
01:16 AM
1 Kudo
@NagendraKumar MSCK Repair which is a DDL operation can be run through PutHiveQL processor. For info on the classification of a Hive command, refer to https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
... View more
09-24-2024
01:14 AM
2 Kudos
@NagendraKumar TRUNCATE is basically a DDL operation which you can run using PutHiveQL processor. For info on the classification of a Hive command, refer to https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
... View more
09-10-2024
06:25 AM
1 Kudo
Do we get this error after it gets connected? Could there be a socket timeout error set at client side? I am not aware if the hive driver has a default timeout value. Instead of ZooKeeper, could you try connecting to HS2 directly. e.g. host: "hs2 node fqdn" port: 10001 Remove serviceDiscoveryMode: 'zooKeeper', zooKeeperNamespace: 'hiveserver2'
... View more
09-09-2024
12:36 AM
1 Kudo
@Viki_Nodejs It seems like the hostname resolution fails. The url you have shared is rather jdbc compliant. Could you give the following a try? client.connect(
{
host: 'abc.com',
port: 2181
},
new hive.connections.HttpConnection({
transportMode: 'http',
httpPath: 'cliservice',
serviceDiscoveryMode: 'zooKeeper',
zooKeeperNamespace: 'hiveserver2'
}),
new hive.auth.PlainHttpAuthentication({
username: 'username',
password: 'password'
}),
{
ssl: true,
sslTrustStore: '/etc/hive-jks/hivetrust.jks',
trustStorePassword: 'pro@!23'
}
).then(async client => {
... View more
08-18-2024
12:52 PM
Until now, you could query JDBC data sources from Hive using JDBC Storage Handler. However, with the release of CDP 7.2.18 (Public Cloud runtime) and 7.1.9 SP 1 (Private Cloud Base), you can also query Hive tables hosted in a different environment using a similar method. Here's a brief demo:
0: jdbc:hive2://ccycloud-1.sbiswal.root.como> CREATE EXTERNAL TABLE loc_hive_table(c1 int, c2 string) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES(
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.database.type"="HIVE",
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.jdbc.driver"="org.apache.hive.jdbc.HiveDriver",
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.jdbc.url"="jdbc:hive2://sbiswal-jdbctest-master0.sup-defa.xxxxxxx.a4.cloudera.site/db1;ssl=true;transportMode=http;httpPath=sbiswal-jdbctest/cdp-proxy-api/hive",
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.dbcp.username”= ”user1”,
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.dbcp.password"="********",
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.table"="src_tbl",
. . . . . . . . . . . . . . . . . . . . . . .> "hive.sql.dbcp.maxActive"="1");
0: jdbc:hive2://ccycloud-1.sbiswal.root.como> select * from loc_hive_table;
INFO : Compiling command(queryId=hive_20240818172147_9c14d21e-1802-43a6-b5a4-0846ff5bad7f): select * from loc_hive_table
INFO : Semantic Analysis Completed (retrial = false)
INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:loc_hive_table.c1, type:int, comment:null), FieldSchema(name:loc_hive_table.c2, type:string, comment:null)], properties:null)
INFO : Completed compiling command(queryId=hive_20240818172147_9c14d21e-1802-43a6-b5a4-0846ff5bad7f); Time taken: 10.033 seconds
INFO : Executing command(queryId=hive_20240818172147_9c14d21e-1802-43a6-b5a4-0846ff5bad7f): select * from loc_hive_table
INFO : Completed executing command(queryId=hive_20240818172147_9c14d21e-1802-43a6-b5a4-0846ff5bad7f); Time taken: 0.02 seconds
INFO : OK
+---------------------+---------------------+
| loc_hive_table.c1 | loc_hive_table.c2 |
+---------------------+---------------------+
| 1 | abc |
| 2 | def |
+---------------------+---------------------+
2 rows selected (12.581 seconds)
0: jdbc:hive2://ccycloud-1.sbiswal.root.como>
The main point to highlight here is hive.sql.database.type"="HIVE.
In this example, a Hive table db1.src_tbl in a remote cluster. To read the table from a different Hive cluster, an external table loc_hive_table had to be created leveraging JDBCStorageHandler. As you can see, the data was read successfully.
If the table to be queried is under a non-default database, it can be defined as follows:
Mention the DB name in the JDBC connection string, as used above: "hive.sql.schema"="db1.test1":
You could query the table from a secured cluster using LDAP authentication. Please be informed that you can only read and not write to the remote Hive table using this approach.
If you want to use any aggregate function in the query and encounter any of the following errors in the YARN application, we may have to add some jars at the session level.
java.lang.ClassNotFoundException: org.apache.hive.jdbc.HiveDriver
java.lang.NoClassDefFoundError: org/apache/hive/service/cli/HiveSQLException
Add equivalent versions of these jars from your local parcel directory,
0: jdbc:hive2://ccycloud-1.sbiswal.root.como> ADD JAR /opt/cloudera/parcels/CDH/lib/hive/lib/hive-jdbc-3.1.3000.7.1.9.1000-103.jar;
0: jdbc:hive2://ccycloud-1.sbiswal.root.como> ADD JAR /opt/cloudera/parcels/CDH/lib/hive/lib/hive-service-3.1.3000.7.1.9.1000-103.jar;
Now you should be able to run your query successfully.
... View more
08-05-2024
03:31 AM
1 Kudo
@evanle96 Hive would treat the timestamp data to be in UTC format, and when we read it, it converts it from UTC to local time. But I understand that the actual data is already in America/Sao_Paulo tz. It'd be interesting to know how do we transform the epoch data in string format to timestamp in ORC? from_utc_timestamp(to_utc_timestamp(dt_afast_trabalho, 'America/Sao_Paulo'), 'America/Sao_Paulo') AS sao_paulo_time_corrected Can we try reading with the above transformation and see if the house field shows up as expected?
... View more
07-25-2024
08:34 PM
1 Kudo
@AhXian I hope we have copied the bucket files correctly, and also ran MSCK REPAIR. Could you also run ANALYZE.. COMPUTE STATISTICS on the external table once. Make sure the DESCRIBE command on the external table reflects the number of data files correctly. Please share describe and "hdfs dfs -ls -R <externa table location>" o/p
... View more
07-25-2024
06:12 AM
@AhXian 1. Are you able to read data from the external table in Prod(step 4)? 2. In step 5, I meant for you to insert data into a new ACID table, which can be created using similar DDL as the original table. 3. After INSERT(step 5), see if you could read the data from the new table. Ideally it should work
... View more
07-24-2024
05:43 AM
Yes, 5 steps mentioned above
... View more
07-24-2024
12:15 AM
In Hive ACID tables, the base/delta files are associated with a writeID. My assumption is the write ID has changed or got removed in the production HMS, so hive does not recognize the data file. So, we will have to follow the workaround that I mentioned in my last update.
... View more