Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.