HIVE create partition

HIVE partition by enabling the Partitionby implementation when the table is created, the dimension used to partition is not a column of the actual data, and the partition-specific flag is given when the content is inserted. When you want to query the contents of a partition, you can use the where statement, which resembles where Tablename.partition_key >a to implement.

Create a table with partitions.

Command prototypes:

CREATE TABLE Page_view (viewtime INT, UserID BIGINT,

Page_urlstring, Referrer_url STRING,

IP stringcomment ' ip Address of the User ')

COMMENT ' This isthe Page view table '

Partitioned by (dtstring, country STRING)

Clusteredby (userid) SORTED by (Viewtime) to BUCKETS

ROW formatdelimited

fieldsterminated by ' \001 '

COLLECTION itemsterminated by ' \002 '

MAP keysterminated by ' \003 '

STORED Assequencefile;

Eg:

Build table:

CREATE TABLE C02_CLICKSTAT_FATDT1

(YYYYMMDD string,

ID INT,

IP String,

Country String,

cookie_id String,

page_id String,

clickstat_url_id int,

Query_string string,

Refer string

) partitioned by (DT STRING)

Row format delimited fields terminated by ' \005 ' stored astextfile;

Loading data:

LOAD DATA inpath '/user/admin/sqlldrdat/cnclickstat/20101101/19/clickstat_gp_fatdt0/0 ' Overwriteinto TABLE c02_ Clickstat_fatdt1

PARTITION (dt= ' 20101101 ');

Access a partition

SELECT COUNT (*)

Fromc02_clickstat_fatdt1 A

WHERE a.dt >= ' 20101101 ' and A.dt < ' 20101102 ';

HIVE create partition