create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

Field interpretation:

Row format defined fields terminated by ',' -- column separator

collection items terminated by '_' -- Separator for map structure and ARRAY (data split symbol)

map keys terminated by ':'

--Separator between key and value in MAP

lines terminated by '\n'; -- line separator

example:

First, prepare the data: create a new test file test txt

Load local file data into hive table

load data local inpath '/usr/local/apache-hive-1.2.1-bin/test.txt' into table test;

View content

Different structure access methods

3. Database and table operations

3.1. Database operation

Default storage path of database on HDFS: / user / hive / warehouse / * db

create database db_hive;                       --Create database
create database if not exists db_hive ;        --Standard writing: avoid that the database already exists, if not exists Used to determine whether the database exists
show databases like 'db_*';                    --Fuzzy search database
use db_hive;                                              --Switch database;
create database db_hive location '/db_hive.db';         --Specify the database in hdfs Storage path on
show databases like 'db_hive*';                          --Fuzzy query database
desc databases db_hive;                                  --Display database information
desc database extended db_hive;     --Display database details (see creation time)
alter database db_hive set dbproperties('createtime'='21823');--Set properties for the database through key value pairs
drop database db_hive;                                  --Delete database
drop database if exists db_hive4;             --use if exists Judge whether the database exists before deleting it

3.2 table operation

Syntax:

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]