hive metadata usage example.

hive-meta.sql

-- Hive Metadata 一般会存储在MySQL中, 所对应的表约20个。

-- * TBLS    table name
-- * TABLE_PARAM      table properties: is it an external table? or comment etc.
-- * COLUMNS      all columns information 
-- * SDS     serde information 
-- * SERDE_PARAM,   serde information
-- * PARTITIONS     partitions
-- * PARTITION_KEYS      keys of partition
-- * PARTITION_KEYS_VALS   values of partition



CREATE TABLE aaa ( bbb INT, bar STRING);
 -- clone an existed table structure:
CREATE TABLE bbb LIKE aaa;

-- view
CREATE VIEW view_aaa AS
SELECT * FROM aaa;


-- external table

CREATE EXTERNAL TABLE pv (
	view_timestamp bigint,
	userid bigint,
	ip string,
	country string )
COMMENT 'test comment table'
PARTITIONED BY (udt string)
ROW FORMAT DELIMTED FIELDS TERMINATED BY '\u0001'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';




-- show 
SHOW TABLES;
SHOW FUNCTIONS;
-- show describe

DESCRIBE tbl_aaa;



-- alter

ALTER TABLE old_name RENAME to new_name;

ALTER TABLE aaa ADD COLUMNS (new_col STRING);

ALTER TABLE aaa ADD IF NOT EXISTS PARTITION (udt = ...); 



-- export data to hdfs


INSERT OVERWRITE LOCAL DIRECTORY <directory>
SELECT ... FROM ...

Hive Metadata 一般会存储在MySQL中, 所对应的表约20个。