@hxn We should not have any hiveserver2 under "Hive" service. hiveserver2 should be present only under "Hive on Tez".   If you have a hiveserver2 instance under "Hive" service, please delete it. ... View more

@dr If it's a managed table, you could get its size from TABLE_PARAMS table: e.g. SELECT a.TBL_NAME AS `TABLE`, b.PARAM_VALUE AS `SIZE` from TABLE_PARAMS b INNER JOIN TBLS a where a.TBL_ID=b.TBL_ID and b.PARAM_KEY='totalSize';   You could change the you need it. But, if there are external tables, or the table stats are not generated regularly, then you might not get the correct data.   You could get the table size using HDFS file system commands as well:   hdfs dfs -du -s -h <path to the table location>   This will give you more accurate data. ... View more

@Shab are we talking about the "Metastore canary failed to create a database" error or the warning messages you have pasted here?    If we are addressing the canary error, please check the Service Monitor logs if you find any relevant error or warning messages.  If you notice any timeout error, you could increase 'hive.metastore.client.socket.timeout ' value to, say, 5 mins, under the "Service Monitor Client Config Overrides" in Hive configuration.   Or if it's a different error, please paste them here. Service Monitor log file location : /var/log/cloudera-scm-firehose/   Refer to https://community.cloudera.com/t5/Support-Questions/The-Hive-Metastore-canary-failed-to-create-a-database/td-p/81021, if that helps. ... View more

@enirys  You could refer to the following doc for Hive tuning: https://docs.cloudera.com/HDPDocuments/HDP2/HDP-2.6.5/bk_hive-performance-tuning/content/ch_connectivity-admission-control.html#guidelines-hiveserver2-heaps   If you have other services running on the same HS2 node as well, you might want to reduce Hive heap size  or move a service to a different node. Are you expecting too many connections as explained in the above doc, else you might want to bring down HS2 heap size? If you do not see too many connections, but notice high heap usage, you might want to take a heap dump as @asish mentioned, and see if there is memory leak.   Load balancing across HS2 could take place based on how you are accessing Hive. You could use zooKeeper based connection string. ... View more

@enirys htop lists every single thread as a separate process. Every individual connection to HS2 and HMS shows up as different processes. So, you do not need to worry about that.   As you say, you have 3 hiveserver2 nodes, see if you could reduce heap size of the hiveserver2. You could also lower 'hive.server2.thrift.max.worker.threads' so, it does not spin up too many threads in a single hs2 node. Make sure that your workload is getting distributed across HS2s.   ... View more

@Anitauzanna partition column will appear at the end of the table if you query it from hive cli or beeline. But partition col data will not be part of the actual table data when you check in hdfs. They will appear as directories in hdfs filesystem.   Answering your second question, yes, you could use substring(or regexp_extract)  to take part of a column and use it as a partition. check : https://community.cloudera.com/t5/Support-Questions/Hive-partitions-based-on-date-from-timestamp/td-p/179583 ... View more

@Anitauzanna sure  you could do that.   I believe you have date in the int format, e.g. 20210927 . Correct me if I am wrong. You could create a non-partitioned table and load the dataset into it. Once done, create the final partitioned table, with the 2nd column as a partition.   e.g.   CREATE TABLE table_final (col1 int, col3 int) PARTITIONED BY (col2 int) STORED AS orc;   Set the following Hive properties, and then load the data to the new partitioned table:   SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict; INSERT OVERWRITE TABLE table_final PARTITION(col2) SELECT col1,col3,col2 FROM <first table>; I hope this answers your question. If it does please accept this as the solution.   ... View more