本文共 7147 字,大约阅读时间需要 23 分钟。
数值:int、bigint、float、double
字符串:string时间:timestamp 不建议使用时间类型,可以使用string布尔:boolean 可以使用string代替 0/1创建数据库语法:
1CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name 2 [COMMENT database_comment] 3 [LOCATION hdfs_path] 4 [WITH DBPROPERTIES (property_name=property_value, ...)]; 5示例: 6hive (default)> create database ruozedata; 7OK 8Time taken: 0.194 seconds 9如果创建已存在的数据库,则会报错10hive (default)> create database ruozedata;11FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database ruozedata already exists12创建数据库的时候加上 if not exists 就不会报错,生厂上使用13hive (default)> create database if not exists ruozedata;14OK15Time taken: 0.011 seconds16不指定创建数据库路径,默认创建在/user/hive/warehouse目录下,有一个数据库名.db的文件夹17[hadoop@hadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse18Found 1 items19drwxr-xr-x - hadoop supergroup 0 2019-07-17 10:36 /user/hive/warehouse/ruozedata.db20指定创建数据库的目录21hive (default)> create database if not exists ruozedata_02 location '/ruozedata_02';22OK23Time taken: 0.046 seconds24[hadoop@hadoop001 ~]$ hdfs dfs -ls /25Found 5 items26-rw-r--r-- 1 hadoop supergroup 7 2019-07-15 19:19 /1.log27drwxr-xr-x - hadoop supergroup 0 2019-07-15 19:35 /example28drwxr-xr-x - hadoop supergroup 0 2019-07-17 10:47 /ruozedata_0229drwx------ - hadoop supergroup 0 2019-07-15 20:10 /tmp30drwxr-xr-x - hadoop supergroup 0 2019-07-16 16:57 /user31可以看到创建数据库的时候指定路径,会在HDFS对应的目录创建相应的文件夹
删除数据库语法:
1DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; 2hive (default)> drop database ruozedata; 3OK 4Time taken: 0.246 seconds 5如果数据库下存在表,删除数据库会报错 6hive (ruozedata_02)> drop database ruozedata_02; 7FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database ruozedata_02 is not empty. One or more tables exist.) 8可使用cascade级联删除,慎用!!! 9hive (ruozedata_02)> drop database ruozedata_02 cascade;10OK11Time taken: 1.264 seconds
切换数据库语法:
1USE database_name;2hive (ruozedata_02)> use default;3OK4Time taken: 0.02 seconds5hive (default)>
创建表语法:
1CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later) 2 [(col_name data_type [COMMENT col_comment], ... [constraint_specification])] 3 [COMMENT table_comment] 4 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 5 [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 6 [SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)] 7 ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) 8 [STORED AS DIRECTORIES] 9 [ 10 [ROW FORMAT row_format] 11 [STORED AS file_format] 12 | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later) 13 ] 14 [LOCATION hdfs_path] 15 [TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later) 16 [AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables) 17 18CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name 19 LIKE existing_table_or_view_name 20 [LOCATION hdfs_path]; 21 22data_type 23 : primitive_type 24 | array_type 25 | map_type 26 | struct_type 27 | union_type -- (Note: Available in Hive 0.7.0 and later) 28 29primitive_type 30 : TINYINT 31 | SMALLINT 32 | INT 33 | BIGINT 34 | BOOLEAN 35 | FLOAT 36 | DOUBLE 37 | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later) 38 | STRING 39 | BINARY -- (Note: Available in Hive 0.8.0 and later) 40 | TIMESTAMP -- (Note: Available in Hive 0.8.0 and later) 41 | DECIMAL -- (Note: Available in Hive 0.11.0 and later) 42 | DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later) 43 | DATE -- (Note: Available in Hive 0.12.0 and later) 44 | VARCHAR -- (Note: Available in Hive 0.12.0 and later) 45 | CHAR -- (Note: Available in Hive 0.13.0 and later) 46 47array_type 48 : ARRAY < data_type > 49 50map_type 51 : MAP < primitive_type, data_type > 52 53struct_type 54 : STRUCT < col_name : data_type [COMMENT col_comment], ...> 55 56union_type 57 : UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later) 58 59row_format 60 : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] 61 [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 62 [NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later) 63 | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 64 65file_format: 66 : SEQUENCEFILE 67 | TEXTFILE -- (Default, depending on hive.default.fileformat configuration) 68 | RCFILE -- (Note: Available in Hive 0.6.0 and later) 69 | ORC -- (Note: Available in Hive 0.11.0 and later) 70 | PARQUET -- (Note: Available in Hive 0.13.0 and later) 71 | AVRO -- (Note: Available in Hive 0.14.0 and later) 72 | JSONFILE -- (Note: Available in Hive 4.0.0 and later) 73 | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname 74 75constraint_specification: 76 : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ] 77 [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 78创建表 79hive (default)> create table emp( 80 > empno int, 81 > ename string, 82 > job string, 83 > mgr int, 84 > hiredate string, 85 > sal double, 86 > comm double, 87 > deptno int) 88 > row format delimited fields terminated by ','; 89OK 90Time taken: 0.083 seconds 91 92Create Table As Select (CTAS) 93创建表并导入数据 94hive (default)> create table emp_01 as select *from emp; 95需要先创建表,再导入数据 96INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement; 97INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement; 98hive (default)>insert into table emp02 select * from emp; 99100Create Table Like101CREATE TABLE empty_key_value_store102LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];103创建表但只创建表结构,无数据104hive (default)> create table emp_02 like emp;105OK106Time taken: 0.114 seconds
重命名表名语法:
1ALTER TABLE table_name RENAME TO new_table_name;2hive (default)> alter table emp_02 rename to rmp_02_old;3OK4Time taken: 0.149 seconds
删除表语法:
1删除表数据跟表 2DROP TABLE [IF EXISTS] table_name [PURGE]; -- (Note: PURGE available in Hive 0.14.0 and later) 3hive (default)> drop table rmp_02_old; 4OK 5Time taken: 0.389 seconds 6删除表的数据,表还在 7TRUNCATE TABLE table_name [PARTITION partition_spec]; 8partition_spec: 9 : (partition_column = partition_col_value, partition_column = partition_col_value, ...)10hive (default)> truncate table emp_01;11OK12Time taken: 0.086 seconds
欢迎关注公众号
转载地址:http://rzqof.baihongyu.com/