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)。