EDIT : Starting with Hive 3.0.0 MSCK can now discover new partitions or remove missing partitions (or both) using the following syntax : MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS] This was implemented in HIVE-17824 As correctly stated by HakkiBuyukcengiz, MSCK REPAIR doesn’t remove partitions if the corresponding folder on HDFS was manually deleted, it only … Read more
Since 0.14, you can use following statement to move table from one database to another in the same metastore: use old_database; alter table table_a rename to new_database.table_a The above statements will also move the table data on hdfs if table_a is a managed table.
You can use the percentile function to compute the median. Try this: select percentile(cast(age as BIGINT), 0.5) from table_name
Found the solution: ALTER TABLE tableA CHANGE ts ts BIGINT AFTER id; See this for complete details: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterColumn
We cannot pass the Hive table name directly to Hive context sql method since it doesn’t understand the Hive table name. One way to read Hive table in pyspark shell is: from pyspark.sql import HiveContext hive_context = HiveContext(sc) bank = hive_context.table(“default.bank”) bank.show() To run the SQL on the hive table: First, we need to register … Read more
Looks like problem with your metastore. If you are using the default hive metastore embedded derby. Lock file would be there in case of abnormal exit. if you remove that lock file this issue would be solved rm metastore_db/*.lck
As per my knowledge, multi-line comments are not supported in Hive scripts as of now. Seems like this JIRA introduced only single line comments, starting with — in Hive 0.8
Change Column Name/Type/Position/Comment: ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] Example: CREATE TABLE test_change (a int, b int, c int); // will change column a’s name to a1 ALTER TABLE test_change CHANGE a a1 INT;
Create Table as select (CTAS) is possible in Hive. You can try out below command: CREATE TABLE new_test row format delimited fields terminated by ‘|’ STORED AS RCFile AS select * from source where col=1 Target cannot be partitioned table. Target cannot be external table. It copies the structure as well as the data Create … Read more
As of Hive v0.13.0, you can use skip.header.line.count table property: create external table testtable (name string, message string) row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’ location ‘/testtable’ TBLPROPERTIES (“skip.header.line.count”=”1”); Use ALTER TABLE for an existing table: ALTER TABLE tablename SET TBLPROPERTIES (“skip.header.line.count”=”1”); Please note that while it works it comes with … Read more