大数据--HiveQL语句(基本操作)

一.数据库操作

1.显示当前所有数据库

Show databases;

2.创建数据库study

CREATE DATABASE IF NOT EXISTS study

COMMENT "This is study database"

LOCATION '/user/hive_db/create_db/';

3.切换数据库

USE study;

4.删除数据库

DROP DATABASE IF EXISTS study;

二.数据表操作

1.创建数据表

(1)概念

数据表是Hive存储数据的基本单位，Hive数据表主要分为内部表（又叫托管表）和外部表，以内部表和外部表为基础可以创建分区表或分桶表，即内/外部分区表或内/外部分桶表。

①内部表

数据由Hive自身管理，数据文件存储在Hive配置文件中参数hive.metastore.warehouse.dir指定的HDFS路径

(/user/hive_local/warehouse)。当删除内部表时，内部表的元数据和数据文件会一同删除。

②外部表

数据由HDFS管理，数据文件存储在创建表时LOCATION子句指定的HDFS路径，若不指定则存储在Hive配置文件中参数hive.metastore.warehouse.dir指定的HDFS路径。当删除外部表时，外部表只会删除元数据，不会删除数据文件。

(2)在study中创建内部表managed_table

CREATE  TABLE IF NOT EXISTS 
study.managed_table(
staff_id INT COMMENT "This is staffid",
staff_name STRING COMMENT "This is staffname",
salary FLOAT COMMENT "This is staff salary",
hobby ARRAY<STRING> COMMENT "This is staff hobby",
deductions MAP<STRING, FLOAT> COMMENT "This is staff deduction",
address STRUCT<street:STRING,city:STRING> COMMENT "This is staff address")
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '_'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS textfile
TBLPROPERTIES("comment"="This is a managed table");

(3)在study中创建外部表external_table

CREATE EXTERNAL TABLE IF NOT EXISTS 
study.external_table(
staff_id INT COMMENT "This is staffid",
staff_name STRING COMMENT "This is staffname",
salary FLOAT COMMENT "This is staff salary",
hobby ARRAY<STRING> COMMENT "This is staff hobby",
deductions MAP<STRING, FLOAT> COMMENT "This is staff deduction",
address STRUCT<street:STRING,city:STRING> COMMENT "This is staff address")
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '_'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/user/hive_external/external_table/'
TBLPROPERTIES("comment"="This is a external table");

2.查看数据表

(1)显示当前数据库所有数据表

SHOW TABLES;

(2)查看指定数据表的结构信息(详细:formatted)

DESC [formatted] managed_table;

3.删除数据表

DROP TABLE IF EXISTS managed_table PURGE;

分桶表,分区表,临时表,视图,索引等待更新~