Hive和YDB的sql语句

最新推荐文章于 2024-04-06 09:58:04 发布

翻译最新推荐文章于 2024-04-06 09:58:04 发布 · 731 阅读

YDB 专栏收录该内容

3 篇文章

订阅专栏

本文介绍YDB与Spark之间的数据交互过程，包括如何创建YDB表、映射表及外部表，并演示了数据导入与查询操作。

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"
);