基于zeppelin使用hive或Flink查询hudi的数据

你的名字 2023-01-17 09:17 147阅读 0赞

**目录**

目标：

一，导入jar

二，创建hudi表：

mor：

cow：

写入数据：

三，创建hive外部表

四，查询结果

五，问题总结

最后提醒：

--------------------

# 目标： #

像iceberg一样，在hive能直接查询hudi表数据。

# 一，导入jar #

将hudi-hadoop-mr-bundle-0.9.0-SNAPSHOT.jar 放入到hive lib下面；

![20210428160945774.png][]

# 二，创建hudi表： #

## mor： ##

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70][]

## cow： ##

>   
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 1][]

## 写入数据： ##

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 2][]

# 三，创建hive外部表 #

在hive shell执行，创建外部表：

CREATE EXTERNAL TABLE hudi_db.hudi_test2(               
       `_hoodie_commit_time` string,                    
       `_hoodie_commit_seqno` string,                   
       `_hoodie_record_key` string,                     
       `_hoodie_partition_path` string,                 
       `_hoodie_file_name` string,                      
       `uuid` STRING,
       `ts` bigint)                                     
     PARTITIONED BY (                                   
       `partition` string)                              
     ROW FORMAT SERDE                                   
       'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'  
     STORED AS INPUTFORMAT                              
       'org.apache.hudi.hadoop.realtime.HoodieParquetRealtimeInputFormat' 
     OUTPUTFORMAT                                       
       'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' 
     LOCATION                                           
       'hdfs://bi-524:8020/tmp/hudi/hudi_test2';

添加分区

alter table hudi_db.hudi_test2 add if not exists partition(`partition`='par1') location 'hdfs://bi-524:8020/tmp/hudi/hudi_test2/par1'

# 四，查询结果 #

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 3][]

# 五，问题总结     #

*不懂的看文章：*

[https://blog.csdn.net/u010848845/article/details/115701329][https_blog.csdn.net_u010848845_article_details_115701329]

1，我这里是hive2.1版本，使用cow的方式查询到了数据， mor没有

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 4][]

官方解释：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 5][]

Mor也会生成parquet 但是要等到compaction，默认checkpoint 5次才会触发（这个参数值也可以修改）。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 6][]

也可以看看 org.apache.hudi.configuration.FlinkOptions 参数有没有设置的，官网参数地址：[http://hudi.apache.org/docs/configurations.html\#write-options-1][http_hudi.apache.org_docs_configurations.html_write-options-1]

还可以参考文章：

[https://blog.csdn.net/hjl18309163914/article/details/116057379][https_blog.csdn.net_hjl18309163914_article_details_116057379]

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 7][]

2，创建hudi表 好像必须要有ts字段

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 8][]

其他：

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 9][]
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 10][]
> 
> .
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 11][]

5，hudi目前是大小写敏感，错误信息会导致JM插入数据时候挂掉

Caused by: java.lang.IllegalArgumentException: No enum constant org.apache.hudi.common.model.HoodieTableType.Copy\_On\_Write  
        at java.lang.Enum.valueOf(Enum.java:238) ~\[?:1.8.0\_162\]  
        at org.apache.hudi.common.model.HoodieTableType.valueOf(HoodieTableType.java:30) ~\[hudi-flink-bundle\_2.11-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT\]  
        at org.apache.hudi.common.table.HoodieTableMetaClient$PropertyBuilder.setTableType(HoodieTableMetaClient.java:613) ~\[hudi-flink-bundle\_2.11-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT\]  
        at org.apache.hudi.util.StreamerUtil.initTableIfNotExists(StreamerUtil.java:262) ~\[hudi-flink-bundle\_2.11-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT\]  
        at org.apache.hudi.sink.StreamWriteOperatorCoordinator.start(StreamWriteOperatorCoordinator.java:154) ~\[hudi-flink-bundle\_2.11-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT\]  
        at org.apache.flink.runtime.operators.coordination.OperatorCoordinatorHolder.start(OperatorCoordinatorHolder.java:189) ~\[flink-dist\_2.11-1.12.2.jar:1.12.2\]  
        at org.apache.flink.runtime.scheduler.SchedulerBase.startAllOperatorCoordinators(SchedulerBase.java:1253) ~\[flink-dist\_2.11-1.12.2.jar:1.12.2\]  
        at org.apache.flink.runtime.scheduler.SchedulerBase.startScheduling(SchedulerBase.java:624) ~\[flink-dist\_2.11-1.12.2.jar:1.12.2\]  
        at org.apache.flink.runtime.jobmaster.JobMaster.startScheduling(JobMaster.java:1032) ~\[flink-dist\_2.11-1.12.2.jar:1.12.2\]  
        at java.util.concurrent.CompletableFuture.uniRun(CompletableFuture.java:705) ~\[?:1.8.0\_162\]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 12][]

# 最后提醒： #

***如果你创建表没问题，数据查询不到，试试建表类似设置为mor 或者cow试试   
  
参考文章：***

[https://blog.csdn.net/u010848845/article/details/115701329][https_blog.csdn.net_u010848845_article_details_115701329]

[https://hudi.apache.org/docs/flink-quick-start-guide.html][https_hudi.apache.org_docs_flink-quick-start-guide.html]

[20210428160945774.png]: /images/20221021/454c3012f334434d8bac66d0cea42f74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70]: /images/20221021/85489bf44299451680c524edcb3f4c73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 1]: /images/20221021/192040ff483b40e89e2e1fd19bc05792.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 2]: /images/20221021/747e9093b37d4e18b122dbc93f993ed1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 3]: /images/20221021/66380cb3df2843f480e97ceee5ee2e9c.png
[https_blog.csdn.net_u010848845_article_details_115701329]: https://blog.csdn.net/u010848845/article/details/115701329
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 4]: /images/20221021/6353d37fd21645e2bb0bb0ab553c8a35.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 5]: /images/20221021/e36034af19044488a0966f83adc55e61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 6]: /images/20221021/36e916c169a6486cbd13daf6e2fe7380.png
[http_hudi.apache.org_docs_configurations.html_write-options-1]: http://hudi.apache.org/docs/configurations.html#write-options-1
[https_blog.csdn.net_hjl18309163914_article_details_116057379]: https://blog.csdn.net/hjl18309163914/article/details/116057379
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 7]: /images/20221021/fb6191874b274728afa70f022ecabfa5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 8]: /images/20221021/b2e162ee7cb84b84abed7800a4a341df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 9]: /images/20221021/835d9675e7704c05a905ba2e274202b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 10]: /images/20221021/60ed476460f949128df0d1438a61d1f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 11]: /images/20221021/3bc32663a86f41b2a21a93520e0746ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxODY2Nzkz_size_16_color_FFFFFF_t_70 12]: /images/20221021/c9ba6a8f9b7944dfbbae208879fa91b6.png
[https_hudi.apache.org_docs_flink-quick-start-guide.html]: https://hudi.apache.org/docs/flink-quick-start-guide.html