Instruction Operation of hive and creation of Internal and external temporary Table
2. Database: OLTP / / online transaction process, online transaction processing
3. Drop databases if exists mybase / / delete the database
4. Show tables / / display table
5. Create the library with create database mybase / / mysql
6. Create table test (id int, name varchar (20)); / / create a list
7. Select id from test / / View the contents of the table
8. The composition of the two tables: select a. Form tbls. B. * form tbls a.
9. Show databases; / / display database
10. Execute under / soft/hive/bin/: hive and then execute: create database if not exists mybase
11. If you use your own library, execute: use mybase;-> remember to add a semicolon
Show tables;-"display table information
12. Create table: create table test (id int, name varchar (20))
13. View the table structure: desc test
14. Put data into the database: insert into test (id,name) values (1)
15. Select * form test / / View all the contents of the table
The specific process is: create a library: create database mysbase-> use that library use mybase-> create a table create table test (id int, name varchar (20))
Fill-in information and creation syntax of the form:
1. Create table if not exists employee (edi int, nam String,salary String, destination String)
2. Comment 'employee details'
4. Row format delimited fields terminated by'\ t'/ / what is the format separation of this line: (1 tom 12)
5. Lines terminated by'\ n' / / Line feeds 1 tom 12
2 tod 13
6. Stored as textfile; / / is stored as a text file
Dfs-lsr /; / / View their directory structure
-
Desc [ribe] database mybase; / / View database information
Alter database mybase set dbproperties ('created'='xpc'); / / modify the database to add database attributes
Desc database extended mybase; / / displays database extension information. Output cannot be formatted.
Desc extended default.test0; / / display table extension information
Desc formatted default.test0; / / display table formatting information
Desc extended test; / / display the extended information of the table
Create database mybase location'/ x _
Create table default.test0 like mybase.test2; / / copy the table structure
Load data local. / / upload local files to hdfs
Load data'/ x _ into table xx / / move the data files on the hdfs file system.
Insert into mybase.test2 select * from default.test0 where id > 1204; / / copy table data
Create table mybase.test3 as select * from default.test0; / / copy table (table structure + data)
Select all id,name from test2; / / query all records (no duplicates)
Select distinct id,name from test2; / / query all records (remove duplicates)
Copy a table schema that already exists
Create table if ont exists mydb.employees2 like mydb.employees
How to insert a text file locally:
Load data local inpath'/ home/user/sample.txt' overwrite into table tset
Steps to create an external table:
1. Create database mybase; / / create a library
2. Use mybase; / / use the library
3. Create external table etest (id int, name string,age int) row format delimited fields terminated by'\ t 'stored as textfile; / / create an external table
4. Check the parameters of the table: desc formatted etest; check how many tables there are: show tables look at the structure of the table select * from etest
5. Create a temporary table create tempporary table temp (id int, name string,age int) row format delimited fields terminated by'\ t 'stored as textfile
6 、 CTAS:create table as select
7. Truncate: delete all rows and can only be used for internal tables
8. Modify the table delimiter: alter table table_name set serdeproperties ('field,delimi'=',')
9. Modify the location of the table: alter table table name set location 'hdfs:/xxx/x/ table name'
Partition table
-
[manual partitioning-static partitioning]
1. Pre-define partitioned columns and store data in a subdirectory of the table.
two。 Create a partition table
Create table xxx (...) Partitioned by (year int, month int) row format...
3. Add multiple partitions to the partition table (manual partitions)
Alter table partest add partition (year=2016,month=4) partition (year=2016,month=5)
4. Displays the partition of the specified table
Show partitions partest
5. Delete partition
ALTER TABLE partest DROP IF EXISTS PARTITION (year=2014, month=11)
6. Load data into the specified partition directory.
LOAD DATA LOCAL INPATH'.. sample.txt' OVERWRITE INTO TABLE partest PARTITION (year=2016, month=4)
Just query the data for April 2016: select * from table name where year=2016 and month = 4
7. Start dynamic partitions (on by default)
SET hive.exec.dynamic.partition=true; / / start dynamic partitioning.
/ / the default is strict, which is strict mode. At least one partition class should be specified and strict mode should be turned off with the following instruction.
SET hive.exec.dynamic.partition.mode=nonstrict; / / sets the partition mode, not strict.
8. Test dynamic partitioning
Insert into table partest partition (year,month) select id,name,age,year,month from test
