天气数据
概览
重点：探索EsgynDB All-In-One SQL功能 — 使用标准的EsgynDB函数查询结构化和半结构化数据。
通过该演示场景，您可以了解EsgynDB的用户自定义函数，以及如何使用这些函数访问以JSON格式存储的半结构化数据。您将使用同时涉及数据格式和技术的查询，处理存储于Esgyn表和Hive表中的结构化和半结构化数据。
关于本数据集
演示数据集新增了天气数据集。天气数据集是半结构化数据（JSON格式），来自NOAA美国国家环境信息气候数据卫星和信息服务中心的机场气象站数据。使用了每日汇总的数据。关于更多信息，请参见每日汇总文档。
天气数据在Hive表和EsgynDB表都可用。其中包括半结构化（JSON）和结构化格式的数据，以便您使用不同的SQL访问方法探索数据。
该示例数据的Hive schema是WEATHER，包括以下表：
- AIRPORT_WEATHER_CSV — 结构化格式的天气事实数据。
- AIRPORT_WEATHER_JSON — 半结构化（JSON）格式的天气事实数据。
该示例数据集的EsgynDB schema是WEATHER，包括以下表：
- AIRPORT_WEATHER — 结构化格式的天气事实数据。
- AIRPORT_WEATHER_JSON — 半结构化（JSON）格式的天气事实数据。
- MEASUREMENT_FLAGS — 维度表，有助于您将测量标记翻译为描述性文本。
- OBSERVATION_CODES — 维度表，有助于您将观测码翻译为描述性文本。
- QUALITY_FLAGS — 维度表，有助于您将质量标记翻译为描述性文本。
- SOURCE_FLAGS — 维度表，有助于您将源标记翻译为描述性文本。
单击此处，查看具有AIRPORT_WEATHER表中数据的机场列表。
All-In-One SQL
通过EsgynDB，您可以使用标准的SQL语法，访问存储于EsgynDB和Hive表的数据。例如，使用schema.table格式访问EsgynDB表，使用hive.schema.table格式访问Hive表。
示例
假设您想要知道，拉瓜迪亚机场雪下得最大的五天。
使用edbci，对EsgynDB weather.airport_weather表运行以下查询：
SQL>select airport +> , observation_date +> , measurement +> from weather.airport_weather +> where airport = 'LGA' and observation = 'SNOW' +> order by measurement desc +> limit 5; AIRPORT OBSERVATION_DATE MEASUREMENT ------- ---------------- ----------- LGA 2016-01-23 27.00 LGA 2016-02-05 2.00 LGA 2016-02-15 1.00 LGA 2016-03-04 1.00 LGA 2016-02-08 1.00 --- 5 row(s) selected. SQL>
然后，对Hive的hive.weather.airport_weather_csv表运行同样的查询：
SQL>select airport +> , observation_date +> , measurement +> from hive.weather.airport_weather_csv +> where airport = 'LGA' and observation = 'SNOW' +> order by measurement desc +> limit 5; AIRPORT OBSERVATION_DATE MEASUREMENT ---------------------------------------------------------------- ---------------- -------------------- LGA 2016-01-23 27 LGA 2016-02-05 2 LGA 2016-03-04 1 LGA 2016-02-15 1 LGA 2016-02-08 1 --- 5 row(s) selected. SQL>
请注意，两个输出的格式不同。这是由Hive表的定义导致的，您可以使用showddl命令查看该定义：
SQL>showddl hive.weather.airport_weather_csv ; /* Hive DDL */ CREATE TABLE WEATHER.AIRPORT_WEATHER_CSV ( AIRPORT string , STATION string , STATION_NAME string , ELEVATION bigint , LATITUDE bigint , LONGITUDE bigint , OBSERVATION_DATE date , OBSERVATION string , MEASUREMENT bigint , MEASUREMENT_FLAG string , QUALITY_FLAG string , SOURCE_FLAG string , OBSERVATION_TIME timestamp ) stored as textfile ; /* Trafodion DDL */ CREATE EXTERNAL TABLE HIVE.WEATHER.AIRPORT_WEATHER_CSV ( AIRPORT VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , STATION VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , STATION_NAME VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , ELEVATION LARGEINT DEFAULT NULL NOT SERIALIZED , LATITUDE LARGEINT DEFAULT NULL NOT SERIALIZED , LONGITUDE LARGEINT DEFAULT NULL NOT SERIALIZED , OBSERVATION_DATE DATE DEFAULT NULL NOT SERIALIZED , OBSERVATION VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , MEASUREMENT LARGEINT DEFAULT NULL NOT SERIALIZED , MEASUREMENT_FLAG VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , QUALITY_FLAG VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , SOURCE_FLAG VARCHAR(31999 BYTES) CHARACTER SET UTF8 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED , OBSERVATION_TIME TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED ) FOR HIVE.WEATHER.AIRPORT_WEATHER_CSV ; --- SQL operation complete. SQL>
只需对查询进行简单的更改，即可改变数据的格式：
SQL>select cast(airport as char(4)) as airport +> , observation_date +> , cast(measurement as decimal(9,2)) as measurement +> from hive.weather.airport_weather_csv +> where airport = 'LGA' and observation = 'SNOW' +> order by measurement desc +> limit 5; AIRPORT OBSERVATION_DATE MEASUREMENT ---------------- ---------------- ----------- LGA 2016-01-23 27.00 LGA 2016-02-05 2.00 LGA 2016-03-04 1.00 LGA 2016-02-15 1.00 LGA 2016-02-08 1.00 --- 5 row(s) selected. SQL>
使用
通过EsgynDB和Hive表中的结构化数据查询功能，您可以了解在未经过完整数据库设计的情况下添加新的数据集是否有利于您的应用程序，然后确定数据是否与您的业务需求相关。
同时，您也可以根据整体的业务需求，将各个数据迁移至EsgynDB表或保留在Hive表。例如，除了新的EsgynDB应用程序，您可能还有访问Hive表的其他基于Hive的应用程序。
访问半结构化数据集
如果您的数据是半结构化格式的（例如，JSON），您可以使用Java或C++用户定义函数（UDFs）扩展EsgynDB的功能。您也可以使用这些UDF，访问存储于EsgynDB、Hive、HBase表的数据。
EsgynDB支持以下类型的UDF：
- 标量UDFs使用标量输入参数，返回单个标量值或包含多个值的元组。标量UDF可用于SQL表达式（例如，SELECT列表或WHERE子句）。
- 表值UDFs (TVUDFs) 使用标量输入参数，返回表值输出。表值UDF用于
FROM子句。 - 表映射UDF（TMUDFs） 是泛化的表值UDF。TMUDF类似于Map/Reduce操作符（例如HiveQL中的FROM…MAP…REDUCE语法）。
示例
本例使用示例的表值UDF（unjson_obj），访问以JSON格式存储的数据。
数据类似于（每行存储一个JSON文档）：
SQL>select * from hive.weather.airport_weather_json limit 1 ;
JSONDATA
--------------------------------------------------------------------------------------------------------------------------------
{"airport":"LAX","station":"GHCND:USW00023174","station_name":"LOS ANGELES INTERNATIONAL AIRPORT CA US","elevation":29.6,"latitude":33.938,"longitude":-118.3888,"observation_date":"2016-01-01","observation":"PRCP","measurement": 0.00,"measurement_flag":" ","quality_flag":" ","source_flag":"W","observation_time":"2016-01-01 00:00:00.000000"}
--- 1 row(s) selected.
SQL>利用unjson_obj TVUDF，您可以通过指定文档名称，访问该文档中的值。例如：
SQL>select +> * +>from udf +>( seabase.unjson_obj -- name of the UDF +> ( table -- tells EsgynDB that this is a table-valued UDF +> ( select +> jsondata -- column name +> from hive.weather.airport_weather_json +> ) +> , 16 -- max length of string returned from JSON data. +> , 'airport' +> , 'observation' +> , 'observation_date' +> , 'measurement' +> ) +>) +>limit 1 +>; AIRPORT OBSERVATION OBSERVATION_DATE MEASUREMENT ---------------- ---------------- ---------------- ---------------- PSP TMAX 2016-03-10 90 --- 1 row(s) selected. SQL>
您可以使用标准的SQL语法，格式化TVUDF返回的列：
SQL>select +> -- Convert the string data to SQL data types. +> cast( airport as char(4) ) as airport +>, cast( observation as char(4) ) as observation +>, cast( observation_date as date ) as observation_date +>, cast( measurement as decimal(9,2) ) as measurement +>from udf +>( seabase.unjson_obj -- name of table-valued UDF +> ( table -- tells EsgynDB that this is a table-valued UDF
+> ( select +> jsondata +> from hive.weather.airport_weather_json +> ) +> , 16 -- max length of string returned from JSON data. +> , 'airport' +> , 'observation' +> , 'observation_date' +> , 'measurement' +> ) +>) +>limit 1 +>; AIRPORT OBSERVATION OBSERVATION_DATE MEASUREMENT ---------------- ---------------- ---------------- ----------- OTH WT08 2016-03-05 -9999.00 --- 1 row(s) selected. SQL>
查询结构化和半结构化数据
您可以结合并匹配标准的SQL和表值UDF，查询架构化和半结构化格式的EsgynDB、Hive、HBase混合数据。
下表是示例查询的说明，您可以从edbci、EsgynDB Manager Query Workbench或DbVisualizer中运行这些查询。所有的查询文件均位于C:\EsgynDB\queries。查询均添加了很多注释，以便您了解查询的编写和更改。
| 文件 | 查询说明 | 查询类型 |
|---|---|---|
| query100.sql | 1月和2月，有降雪的机场和日期。 | 使用EsgynDB表值用户定义函数（TVUDF），查询存储于Hive表的JSON数据。 |
| query101.sql | 1月和2月，降雪量最大的十个机场（按积雪深度排序）和日期。 | 使用EsgynDB表值用户定义函数（TVUDF），查询存储于Hive表的JSON数据。将查询结果与结构化的EsgynDB维度表join起来。 |
| query102.sql | 2016年的头两个月，报告降雪的机场中，风速最快的机场。 | 对结构化的Hive表进行两次查询。该查询对Hive表使用子查询，识别报告降雪的机场。 |
| query103.sql | 2016年的头两个月，在报告降雪的机场中，取消的航班以及当天报告的最高风速。 | 天气数据存储于结构化的Hive表中。航班信息存储于多个结构化的EsgynDB表中。在where子句中使用多个join和select子句。 |
| query104.sql | 1月和2月，有降雪的机场和降雪的日期。 | 使用unjson_obj表值函数， 查询存储于Hive表的JSON数据。where子句中的select语句用于查询结构化的Hive表，以查询报告降雪的机场和日期。 |
| query125.sql | 探索天气数据，了解不同的观测项/测量值。 | 查询检索天气数据中每项观测的最小/最大值，每个机场的最小/最大日期。 检索到的数据与observation_codes维度表join起来，该维度表提供观察结果的文本描述（包括测量的单位）。 通过查询结果，您可以看到温度、风速等测量值要转换为实际值， 而积雪、雾等其他观测项直接使用测量值作为boolean flag。从而，您可以更好地了解如何使用天气数据构建您自己的查询。 查询使用RANK() OLAP函数，以识别各个机场最小/最大的观测数据。使用UNION，查询最小或最大的值/日期。 |
| query150.sql | 如果有一架航班延误，各机场的平均起飞延误时间。 | 查询使用子查询，计算各机场各天的平均起飞延误时间（周一、周二、周三……）。 数据与存储在Hive表的JSON天气数据join起来，获取该日的降水信息。 |
| query151.sql | 如果有一架航班延误，各机场的平均起飞延误时间。 | 查询使用子查询，计算各机场各天的平均起飞延误时间（周一、周二、周三……）。 数据与存储在EsgynDB表的JSON天气数据join起来，获取该日的降水信息。 |
| query152.sql | 如果有一架航班延误，各机场的平均起飞延误时间。 | 查询使用子查询，计算各机场各天的平均起飞延误时间（周一、周二、周三……）。 数据与存储在结构化Hive表的天气数据join起来，获取该日的降水信息。 |
| query153.sql | 如果有一架航班延误，各机场的平均起飞延误时间。 | 查询使用子查询，计算各机场各天的平均起飞延误时间（周一、周二、周三……）。 数据与存储在结构化EsgynDB表的天气数据join起来，获取该日的降水信息。 |
接下来，我们建议您根据以上的示例查询，编写您自己的查询。您可以使用query125.sql，探索天气数据可用的不同观测数据。
例如，确定降水量超过一（1）英寸的机场（observation =‘PRCP’)。