Hivedata仓libraryBasics

# 1.  under 载Hive
$ wget https://dlcdn.apache.org/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz

# 2. 解压Hive
$ tar -xzvf apache-hive-3.1.3-bin.tar.gz
$ mv apache-hive-3.1.3-bin /opt/hive

# 3. configurationenvironmentvariable
$ export HIVE_HOME=/opt/hive
$ export PATH=$PATH:$HIVE_HOME/bin

# 4. configurationHive
$ cp $HIVE_HOME/conf/hive-env.sh.template $HIVE_HOME/conf/hive-env.sh
$ echo "export HADOOP_HOME=/opt/hadoop" >> $HIVE_HOME/conf/hive-env.sh

# 5. 初始化Metastore (using in 置Derby) 
$ schematool -dbType derby -initSchema

# 1. creationMySQLdatalibrary and user
$ mysql -u root -p
mysql> CREATE DATABASE metastore;
mysql> CREATE USER 'hive'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON metastore.* TO 'hive'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> exit;

# 2. configurationHiveusingMySQL
$ cp $HIVE_HOME/conf/hive-site.xml.template $HIVE_HOME/conf/hive-site.xml
$ vim $HIVE_HOME/conf/hive-site.xml

# 添加以 under configuration

  
    javax.jdo.option.ConnectionURL
    jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true
    JDBC connect string for a JDBC metastore
  
  
    javax.jdo.option.ConnectionDriverName
    com.mysql.jdbc.Driver
    Driver class name for a JDBC metastore
  
  
    javax.jdo.option.ConnectionUserName
    hive
    username to use against metastore database
  
  
    javax.jdo.option.ConnectionPassword
    password
    password to use against metastore database
  


# 3.  under 载MySQL驱动
$ wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.49.tar.gz
$ tar -xzvf mysql-connector-java-5.1.49.tar.gz
$ cp mysql-connector-java-5.1.49/mysql-connector-java-5.1.49-bin.jar $HIVE_HOME/lib/

# 4. 初始化Metastore (usingMySQL) 
$ schematool -dbType mysql -initSchema

# 启动Hive CLI
$ hive

# 显示所 has datalibrary
hive> SHOW DATABASES;

# creationdatalibrary
hive> CREATE DATABASE mydb;

# usingdatalibrary
hive> USE mydb;

# 查看datalibraryinformation
hive> DESCRIBE DATABASE mydb;

# deletedatalibrary
hive> DROP DATABASE IF EXISTS mydb;

# deletepackage含表 datalibrary
hive> DROP DATABASE IF EXISTS mydb CASCADE;

# creation表
hive> CREATE TABLE employees (
    > id INT,
    > name STRING,
    > age INT,
    > department STRING
    > )
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > STORED AS TEXTFILE;

# 显示所 has 表
hive> SHOW TABLES;

# 查看表structure
hive> DESCRIBE employees;

# 查看表详细information
hive> DESCRIBE EXTENDED employees;

# modify表名
hive> ALTER TABLE employees RENAME TO staff;

# 添加列
hive> ALTER TABLE staff ADD COLUMNS (salary FLOAT);

# modify列名 and class型
hive> ALTER TABLE staff CHANGE COLUMN age employee_age INT;

# delete表
hive> DROP TABLE IF EXISTS staff;

# creationexampledatafile
$ echo -e "1,Alice,30,HR\n2,Bob,28,IT\n3,Charlie,35,Finance" > employees.txt

# 加载data to 表in
hive> LOAD DATA LOCAL INPATH 'employees.txt' INTO TABLE employees;

#  from HDFS加载data
hive> LOAD DATA INPATH '/user/hadoop/employees.txt' INTO TABLE employees;

# exportdata to 本地filesystem
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/employees' 
    > SELECT * FROM employees;

# exportdata to HDFS
hive> INSERT OVERWRITE DIRECTORY '/user/hadoop/employees' 
    > SELECT * FROM employees;

#  simple query
hive> SELECT * FROM employees;

# 条件query
hive> SELECT name, age FROM employees WHERE department = 'IT';

# aggregatequery
hive> SELECT department, COUNT(*) as count FROM employees GROUP BY department;

# sortquery
hive> SELECT * FROM employees ORDER BY age DESC;

# 连接query
hive> CREATE TABLE departments (
    > id INT,
    > name STRING,
    > location STRING
    > )
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > STORED AS TEXTFILE;

hive> LOAD DATA LOCAL INPATH 'departments.txt' INTO TABLE departments;

hive> SELECT e.name, d.name, d.location 
    > FROM employees e 
    > JOIN departments d 
    > ON e.department = d.name;

# creationpartition表
hive> CREATE TABLE employees_partitioned (
    > id INT,
    > name STRING,
    > age INT
    > )
    > PARTITIONED BY (department STRING)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > STORED AS TEXTFILE;

# 加载data to partition
hive> LOAD DATA LOCAL INPATH 'hr_employees.txt' INTO TABLE employees_partitioned PARTITION (department='HR');
hive> LOAD DATA LOCAL INPATH 'it_employees.txt' INTO TABLE employees_partitioned PARTITION (department='IT');

# querypartitiondata
hive> SELECT * FROM employees_partitioned WHERE department = 'HR';

# 显示所 has partition
hive> SHOW PARTITIONS employees_partitioned;

# creation分桶表
hive> CREATE TABLE employees_bucketed (
    > id INT,
    > name STRING,
    > age INT,
    > department STRING
    > )
    > CLUSTERED BY (id) INTO 4 BUCKETS
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > STORED AS TEXTFILE;

# 启用分桶
 hive> SET hive.enforce.bucketing = true;

# 插入data to 分桶表
hive> INSERT INTO TABLE employees_bucketed SELECT * FROM employees;

# data抽样
hive> SELECT * FROM employees_bucketed TABLESAMPLE(BUCKET 1 OUT OF 4 ON id);