1:hive是基于hadoop之上的数据仓库
1:hive 启动会同步mysql 创建一个数据库会默认生成一些表
+---------------------------+
| Tables_in_hive_db |
+---------------------------+
| BUCKETING_COLS |
| CDS |
| COLUMNS_V2 |
| DATABASE_PARAMS |
| DBS |
| FUNCS |
| FUNC_RU |
| GLOBAL_PRIVS |
| PARTITIONS |
| PARTITION_KEYS |
| PARTITION_KEY_VALS |
| PARTITION_PARAMS |
| PART_COL_STATS |
| ROLES |
| SDS |
| SD_PARAMS |
| SEQUENCE_TABLE |
| SERDES |
| SERDE_PARAMS |
| SKEWED_COL_NAMES |
| SKEWED_COL_VALUE_LOC_MAP |
| SKEWED_STRING_LIST |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES |
| SORT_COLS |
| TABLE_PARAMS |
| TAB_COL_STATS |
| TBLS |
| VERSION |
+---------------------------+
29 rows in set (0.00 sec)
1:hive 创建一个数据库
hive> CREATE DATABASE zhdc;
OK
Time taken: 0.22 seconds
hive>
2:hive 查看数据库列表
hive> SHOW DATABASES;
OK
default
zhdc
Time taken: 0.032 seconds, Fetched: 2 row(s)
3:hive 存在 hdfs 什么地方?
hive> DESC DATABASE zhdc;
OK
zhdc hdfs://localhost:9000/user/hive/warehouse/zhdc.db hadoop USER
Time taken: 0.032 seconds, Fetched: 1 row(s)
1:hive元数据
mysql> show tables;
+---------------------------+
| Tables_in_ruoze_d6 |
+---------------------------+
| bucketing_cols |
| cds |
| columns_v2 |
| database_params |
| dbs |
| func_ru |
| funcs |
| global_privs |
| part_col_stats |
| partition_key_vals |
| partition_keys |
| partition_params |
| partitions |
| roles |
| sd_params |
| sds |
| sequence_table |
| serde_params |
| serdes |
| skewed_col_names |
| skewed_col_value_loc_map |
| skewed_string_list |
| skewed_string_list_values |
| skewed_values |
| sort_cols |
| tab_col_stats |
| table_params |
| tbls |
| version |
+---------------------------+
29 rows in set (0.00 sec)
2:dbs
mysql> select * from dbs;
+-------+-----------------------+---------------------------------------------------+---------+------------+------------+
| DB_ID | DESC | DB_LOCATION_URI | NAME | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+---------------------------------------------------+---------+------------+------------+
| 1 | Default Hive database | hdfs://localhost:9000/user/hive/warehouse | default | public | ROLE |
| 6 | NULL | hdfs://localhost:9000/user/hive/warehouse/zhdc.db | zhdc | hadoop | USER |
+-------+-----------------------+---------------------------------------------------+---------+------------+------------+
2 rows in set (0.00 sec)
mysql>
1:格式化查询数据
mysql> select * from dbs \G;
*************************** 1. row ***************************
DB_ID: 1
DESC: Default Hive database
DB_LOCATION_URI: hdfs://localhost:9000/user/hive/warehouse
NAME: default
OWNER_NAME: public
OWNER_TYPE: ROLE
*************************** 2. row ***************************
DB_ID: 6
DESC: NULL
DB_LOCATION_URI: hdfs://localhost:9000/user/hive/warehouse/zhdc.db
NAME: zhdc
OWNER_NAME: hadoop
OWNER_TYPE: USER
2 rows in set (0.00 sec)
1:创建表
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
2:执行
hive> create table emp(
> empno int,
> ename string,
> job string,
> mgr int,
> hiredate string,
> sal double,
> comm double,
> deptno int
> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.37 seconds
hive> show tables;
OK
emp
Time taken: 0.036 seconds, Fetched: 1 row(s)
hive>
导入本地文件到hive数据库表
hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/hue' OVERWRITE INTO TABLE database_test.emp;