Hive和YDB的sql语句

YDB的数据类型

| YDB类型 | Hive类型 | 注解 |

| :------- | --------:| :--: |

| String | String | 字符串|

| haoma | String | 用于对号码类型的模糊检索 |

| tint | int | 整形32位|

| tlong | bigint | 整形64位|

| tdouble | double | double类型，浮点型|

| tfloat | float | 小数|

首先需要在ydb的web页面中创建ydb的表，该表为YDB的原始表，创建后可以使用YDB原生的基本查询，

但SPARK中还不能直接使用。

示例如下

打开 http://ycloudtest:1210并打开《创建数据表》连接

create table ydb_example_shu(

phonenum long,

usernick string,

ydb_sex string,

ydb_province string,

ydb_grade string,

ydb_age string,

ydb_blood string,

ydb_zhiye string,

ydb_earn string,

ydb_prefer string,

ydb_consume string,

ydb_day string,

amtdouble tdouble,

amtlong int,

content textcjk

)

该表的作用是用于对接YDB与SPARK，只有创建了该表才能将Spark中的其他表，通过insert方式导入到YDB总，当然如果我们的YDB数据是通过kafka导入的，则不需要创建

CREATE external table ydb_example_shu(

 phonenum bigint, usernickstring, ydb_sexstring,  ydb_province string, ydb_grade string, ydb_age string, ydb_blood string, ydb_zhiye string, ydb_earn string, ydb_prefer string, ydb_consume string, ydb_day string, amtdouble double,amtlong int, content string, ydbpartion string, ya100_pipe string

)

STORED BY 'cn.net.ycloud.ydb.handle.Ya100StorageHandler'

TBLPROPERTIES(

"ya100.handler.table.name"="ydb_example_shu",

"ya100.handler.master"="ydb.zookeeper.mode",

"ya100.handler.columns.mapping"="phonenum,usernick,ydb_sex,ydb_province,ydb_grade,ydb_age,ydb_blood,ydb_zhiye,ydb_earn,ydb_prefer,ydb_consume,ydb_day,amtdouble,amtlong,content,ydbpartion,ya100_pipe"

);

创建外部表

CREATE external  table ydb_import_txt( 
phonenum string, usernick string, ydb_sex string, ydb_province string, ydb_grade string, ydb_age string, ydb_blood string, ydb_zhiye string, ydb_earn string, ydb_prefer string, ydb_consume string, ydb_day string, amtdouble double,amtlong bigint,content string
)
row format delimited fields terminated by ',' 
 stored as INPUTFORMAT 'cn.net.ycloud.ydb.handle.Ya100FixNumCombineTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
 location '/data/example/ydb';

创建映射表

CREATE external  table ydb_example_shu( 
 phonenum string, usernick string, ydb_sex string,  ydb_province string, ydb_grade string, ydb_age string, ydb_blood string, ydb_zhiye string, ydb_earn string, ydb_prefer string, ydb_consume string, ydb_day string, amtdouble double,amtlong bigint, content string, ydbpartion string, ya100_pipe string
)
STORED BY 'cn.net.ycloud.ydb.handle.Ya100StorageHandler'
TBLPROPERTIES(
"ya100.handler.table.name"="ydb_example_shu",
"ya100.handler.master"="ydb.zookeeper.mode",
"ya100.handler.columns.mapping"="phonenum,usernick,ydb_sex,ydb_province,ydb_grade,ydb_age,ydb_blood,ydb_zhiye,ydb_earn,ydb_prefer,ydb_consume,ydb_day,amtdouble,amtlong,content,ydbpartion,ya100_pipe"
);

追加数据到映射表

insert into  table ydb_example_shu select *,'3000w','' from ydb_import_txt;

查询等...

查询语句有几种类型 Hive的

count计数，需要创建一个r[0]的数组定义类型，as别名，

sum求和，需要创建一个r[0]的数组定义类型，as别名，

avg求平均数

查询语句YDB的

and类型，（or），（to）。