Hive 不同类型的数值型数据和对应的范围

如果给数值型数据加后缀，必须是大写。

什么都不加

默认为 int，对应 java 中的 int，数据库中的 int。

hive> explain extended select 9;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 9 (type: int)
            outputColumnNames: _col0
            ListSink

Time taken: 0.167 seconds, Fetched: 17 row(s)

L

L 代表 Long，对应 java 中的 Long 类型，对应数据库的 bigint。占用 8 字节。

hive> explain extended select 9L;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 9L (type: bigint)
            outputColumnNames: _col0
            ListSink

S

S 对应 java 中的 short，数据库的 smallint。占用 2 字节
数据超过范围会溢出。

hive>  select 9S + 32761S;
OK
-32766

hive> explain extended select 9S;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 9S (type: smallint)
            outputColumnNames: _col0
            ListSink

Time taken: 0.378 seconds, Fetched: 17 row(s)

Y

Y 对应的是 java 中的 byte，数据库中的 tinyint。只有1字节。

hive> select 9Y+121Y;
OK
-126

hive> explain extended select 9Y;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 9Y (type: tinyint)
            outputColumnNames: _col0
            ListSink

Time taken: 0.155 seconds, Fetched: 17 row(s)

BD

BD 对应 BigDecimal，数据库中的 decimal 类型。

hive> explain extended select 1.23BD;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 1.23 (type: decimal(3,2))
            outputColumnNames: _col0
            ListSink

Time taken: 0.183 seconds, Fetched: 17 row(s)

D

D 对应 java 中的 double，数据库中的 double 类型。

hive> explain extended select 1.23D;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 1.23D (type: double)
            outputColumnNames: _col0
            ListSink

Time taken: 0.137 seconds, Fetched: 17 row(s)

E e（大小写都可以）

数据的指数表示。

 select 1e2;
OK
100.0
Time taken: 0.165 seconds, Fetched: 1 row(s)

hive> explain extended select 1e2;
OK
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          GatherStats: false
          Select Operator
            expressions: 100.0D (type: double)
            outputColumnNames: _col0
            ListSink

Time taken: 0.157 seconds, Fetched: 17 row(s)