hive SQL 创建数据库，创建hive表、查询时，其表名，字段，统统不区分大写(在底层一律转换为小写)

hive SQL 创建数据库，创建hive表、查询时，其表名，字段，统统不区分大写(在底层一律转换为小写)

（1）、默认default数据库

hive默认自带一个名为default的数据库，如果建表时没有指定你要用哪个数据库，默认你的表就会建到default数据库中。

CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>

在这里，IF NOT EXISTS是一个可选子句，通知用户已经存在相同名称的数据库。可以使用SCHEMA 在DATABASE的这个命令。下面的查询执行创建一个名为userdb数据库：

hive> CREATE DATABASE [IF NOT EXISTS] userdb;

下面的查询用于验证数据库列表：

hive> SHOW DATABASES;

default

userdb

（2）、以下HSql证明创建数据库的时候不区分大小写(内部一律变为小写)

hive> create database DB_TEST;
OK
Time taken: 0.156 seconds
hive> create database Db_test;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database Db_test already exists
hive> create database db_test;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_test already exists
hive>
hive> show databases;
OK
db_test
default

（3）、以下证明创建hive表时候不区分大小写(内部统一转换为小写)

hive> CREATE TABLE `parquet`(
    >   `member_id` string,
    >   `name` string,
    >   `stat_date` string,
    >   `province` string)
    > ROW FORMAT SERDE
    >   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
    > WITH SERDEPROPERTIES (
    >   'field.delim'='\t',
    >   'serialization.format'='\t');
OK
Time taken: 0.814 seconds
hive> show tables;
OK
parquet
Time taken: 0.059 seconds, Fetched: 1 row(s)
hive> use default;
OK
Time taken: 0.019 seconds
hive> show tables;
OK
parquet
Time taken: 0.072 seconds, Fetched: 1 row(s)
hive> desc parquet;
OK
member_id           	string
name                	string
stat_date           	string
province            	string
Time taken: 0.124 seconds, Fetched: 4 row(s)
hive> show create table parquet;
OK
CREATE TABLE `parquet`(
  `member_id` string,
  `name` string,
  `stat_date` string,
  `province` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES (
  'field.delim'='\t',
  'serialization.format'='\t')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://localhost:9002/user/hive/warehouse/parquet'
TBLPROPERTIES (
  'transient_lastDdlTime'='1526610048')
Time taken: 0.151 seconds, Fetched: 18 row(s)
hive> CREATE TABLE `PARquet`(
    >   `Member_id` string,
    >   `NAME` string,
    >   `stat_date` string,
    >   `province` string)
    > ROW FORMAT SERDE
    >   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
    > WITH SERDEPROPERTIES (
    >   'field.delim'='\t',
    >   'serialization.format'='\t');
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. AlreadyExistsException(message:Table PARquet already exists)
hive> CREATE TABLE `PARquet_1`(
    >   `Member_id` string,
    >   `NAME` string,
    >   `stat_date` string,
    >   `province` string)
    > ROW FORMAT SERDE
    >   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
    > WITH SERDEPROPERTIES (
    >   'field.delim'='\t',
    >   'serialization.format'='\t');
OK
Time taken: 0.181 seconds
hive> SHOW TABLES;
OK
parquet
parquet_1
Time taken: 0.051 seconds, Fetched: 2 row(s)
hive> desc parquet_1;
OK
member_id           	string
name                	string
stat_date           	string
province            	string
Time taken: 0.086 seconds, Fetched: 4 row(s)
hive> show create table PARqueT_1;
OK
CREATE TABLE `PARqueT_1`(
  `member_id` string,
  `name` string,
  `stat_date` string,
  `province` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES (
  'field.delim'='\t',
  'serialization.format'='\t')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://localhost:9002/user/hive/warehouse/parquet_1'
TBLPROPERTIES (
  'transient_lastDdlTime'='1526610301')
Time taken: 0.428 seconds, Fetched: 18 row(s)

（4）、以下证明在进行HSql查询的时候不区分大小写（内部一律转换为小写）

hive> select NAMe from PARqueT_1;
OK
Time taken: 1.546 seconds
hive> explain select NAMe from PARqueT_1;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: parquet_1
          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
          Select Operator
            expressions: name (type: string)
            outputColumnNames: _col0
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
            ListSink

Time taken: 0.563 seconds, Fetched: 17 row(s)
hive> select qqNAMe from PARqueT_1;
FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'qqNAMe': (possible column names are: member_id, name, stat_date, province)