创建数据库：

<span style="font-size:18px;">hive> CREATE DATABASE [IF NOT EXISTS] mydb;
hive> CREATE SCHEMA mydb
hive> use mydb</span>

创建表：

<pre name="code" class="plain"><span style="font-size:18px;">hive> create EXTERNAL table IF NOT EXISTS tb(...//外部表
hive> CREATE TABLE table (...//内部表</span>

建表语句示例：

<span style="font-size:18px;">hive> create table customers (id string, name string, email string, street_address string, company string) 
> ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
> with SERDEPROPERTIES ('escape.delim'='\\', 'field.delim'=',', 'serialization.format'=',');</span>

<span style="font-size:18px;">hive> set hive.enforce.bucketing = true
hive> create table customers (id string, name string, email string, street_address string, company string)
> partitioned by (time string)
> clustered by (id) into 5 buckets stored as orc
> location '/user/bedrock/salescust'
> TBLPROPERTIES ('transactional'='true');</span>

Hive可以通过在指定列上建立索引来提高查询速度，可以建立压缩索引与位图索引二种类型的索引，
索引数据保存在另外的表中,可以自定义索引表名也可以取默认值,索引表的基本包含几列：
1. 源表的索引列；
2. _bucketname hdfs中文件地址
3. 索引列在hdfs文件中的偏移量。
原理是通过记录索引列在HDFS中的偏移量，精准获取数据，避免全表扫描

<span style="font-size:18px;">hive> create index customer_index on table customers(id)
> as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild
> in table customer_index_table

hive> create index customer_index on table customers(id)
> as 'org.apache.hadoop.hive.ql.index.bitmap.BitmapIndexHandler' with deferred rebuild stored as rcfile 

hive> alter index customer_index on customers rebuild;//填充索引数据
hive> alter index customer_index on customers partition(columnx='', columny='') rebuild;//在分区上重建索引
hive> show formatted index on customers;
hive> desc customer_index;//查看索引表结构
hive> select * from customer_index_table limit 10; 
hive> drop index customer_index_table on customers;</span>

<span style="font-size:18px;">为了确保Hive能够有效处理事务数据，以下设置要求在Hive配置中进行：</span>

<span style="font-size:18px;">hive> hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</span>

Hive表更新与删除数据(表必须开启事务属性才支持update与delete)：

<span style="font-size:18px;">hive> delete from customer_index where id=1;
hive> truncate table customer_index;</span>

Hive数据导入方式：

<span style="font-size:18px;">hive> load data local inpath '/usr/local/hive/customers.info' (overwrite) into table customers;</span>

SEQUENCEFILE，RCFILE，ORCFILE格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中，然后再从表中用insert导入SequenceFile,RCFile,ORCFile表中

<span style="font-size:18px;">hive> load data inpath '/hive/customers.info' (overwrite) into table customers;</span>

<span style="font-size:18px;">hive> insert into table customers select * from customers_tmp;</span>

<span style="font-size:18px;">hive> create table customers as select * from customers_tmp;</span>

<span style="font-size:18px;">hive> CREATE TABLE customers LIKE customers_tmp;</span>

<span style="font-size:18px;">hive> insert overwrite local directory '/usr/local/hive/customers.info' select * from customers;</span>

<span style="font-size:18px;">hive> insert overwrite directory '/hive/customers.info' select * from customers;</span>