Use beeline with hiveserver2, hive CLI is deprecated.
Abilities: These operations work on tables or partitions
date_partitionof type STRING and
country_partitionof type STRING. Each unique value of the partition keys defines a partition of the Table. For example, all “US” data from “2009-12-23” is a partition of the page_views table. Therefore, if you run analysis on only the “US” data for 2009-12-23, you can run that query only on the relevant partition of the table, thereby speeding up the analysis significantly. Note however, that just because a partition is named 2009-12-23 does not mean that it contains all or only data from that date; partitions are named after dates for convenience; it is the user’s job to guarantee the relationship between partition name and data content! Partition columns are virtual columns, they are not part of the data itself but are derived on load.
page_viewstable may be bucketed by userid, which is one of the columns, other than the partitions columns, of the
page_viewtable. These can be used to efficiently sample the data.
CREATE TABLE page_view(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User') COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' STORED AS SEQUENCEFILE; SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards']; SHOW TABLES [IN database_name] ['identifier_with_wildcards']; SHOW VIEWS; -- show all views in the current database SHOW VIEWS 'test_*'; -- show all views that start with "test_" SHOW VIEWS '*view2'; -- show all views that end in "view2" SHOW VIEWS LIKE 'test_view1|test_view2'; -- show views named either "test_view1" or "test_view2" SHOW VIEWS FROM test1; -- show views from database test1 SHOW VIEWS IN test1; -- show views from database test1 (FROM and IN are same) SHOW VIEWS IN test1 "test_*"; -- show views from database test2 that start with "test_" SHOW PARTITIONS table_name PARTITION(ds='2010-03-03'); -- (Note: Hive 0.6 and later) SHOW PARTITIONS table_name PARTITION(hr='12'); -- (Note: Hive 0.6 and later) SHOW PARTITIONS table_name PARTITION(ds='2010-03-03', hr='12'); -- (Note: Hive 0.6 and later) SHOW TBLPROPERTIES tblname; SHOW TBLPROPERTIES tblname("foo"); SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]; DESCRIBE DATABASE [EXTENDED] db_name; DESCRIBE SCHEMA [EXTENDED] db_name; -- (Note: Hive 1.1.0 and later) DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name[ col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ]; -- (Note: Hive 1.x.x and 0.x.x only. See "Hive 2.0+: New Syntax" below) DESCRIBE FORMATTED default.src_table PARTITION (part_col = 100) columnA; DESCRIBE default.src_thrift lintString.$elem$.myint; INSERT OVERWRITE TABLE pv_gender_agg SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid) FROM pv_users GROUP BY pv_users.gender;