hive

CREATE DATABASE IF NOT EXISTS mydb;

DROP DATABASE mydb [CASCADE];

CREATE TABLE IF NOT EXISTS emp (  id INT,  name STRING,  salary FLOAT)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS TEXTFILE;

CREATE EXTERNAL TABLE IF NOT EXISTS emp_ext (  id INT,  name STRING,  salary FLOAT)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','LOCATION '/user/hadoop/empdata';

DROP TABLE IF EXISTS emp;

LOAD DATA LOCAL INPATH '/home/data/emp.csv' INTO TABLE emp;-- LOCAL 表示从本地上传；省略则是从 HDFS

INSERT INTO TABLE emp VALUES (1, 'Tom', 5000.0);

FROM employeesINSERT INTO TABLE high_salary SELECT * WHERE salary > 8000INSERT INTO TABLE low_salary SELECT * WHERE salary <= 8000;

CREATE TABLE emp_copy AS SELECT * FROM emp;

SELECT id, name FROM emp WHERE salary > 5000;

SELECT * FROM emp ORDER BY salary DESC LIMIT 10;

SELECT dept, COUNT(*) AS cnt, AVG(salary) AS avg_salFROM empGROUP BY deptHAVING COUNT(*) > 5;

SELECT name FROM emp1UNION ALLSELECT name FROM emp2;

SELECT DISTINCT dept FROM emp;

SELECT a.id, a.name, b.dept_nameFROM emp aJOIN dept bON a.dept_id = b.id;

SELECT a.id, a.name, b.dept_nameFROM emp aLEFT JOIN dept b ON a.dept_id = b.id;

RIGHT JOIN / FULL OUTER JOIN

show functions;

vim hive-site.xml

<!-- 数据默认的存储位置(HDFS) --><property>        <name>hive.metastore.warehouse.dir</name>        <value>/user/hive/warehouse</value>        <description>location of default database for the warehouse</description></property><!-- 在命令行中，显示当前操作的数据库 --><property>        <name>hive.cli.print.current.db</name>        <value>true</value>        <description>Whether to include the current database in the Hive prompt.</description></property><!-- 在命令行中，显示数据的表头 --><property>        <name>hive.cli.print.header</name>        <value>true</value></property><!-- 操作小规模数据时，使用本地模式，提高效率 --><property>        <name>hive.exec.mode.local.auto</name>        <value>true</value>        <description>Let Hive determine whether to run in local mode automatically</description></property>

hive: set;

!ls;!clear;

dfs -ls /;

select array(1,2,3);select arr[0] from (select array(1,2,3) arr) tmp;select map('a',1,'b',2,'c',3);select mymap["a"] from (select map('a', 1, 'b', 2, 'c',3) as mymap) tmp;select struct('username1', 7, 1288.68);