Flink SQL 1.12 How to implement query Hbase table on secondary index

[WuKong <wu...@foxmail.com>]

Hi, 
    now my data store hbase and I want use flink to implement kafka table temproal join hbase table , but condtion is not rowkey ,  I realize hbase secondary index, how can I implement this function what can use flink sql first query secondary index(such as es) and then use rowkey query hbase table (must implement own hbase secondary connectors ？) 



---
Best,
WuKong

Re: Re: Flink SQL 1.12 How to implement query Hbase table on secondary index

[WuKong <wu...@foxmail.com>]

Hi Jing Ge:
    this is the example:
 table ddl : 
  CREATE TABLE  dim_db.dim_table (
     column_one string,
    column_two string, 
    column_three string, 
   primary key(column_one) NOT ENFORCED
     ) WITH (
  'table-name' = 'xxx',
  'connector' = 'hbase-1.4',
  'zookeeper.quorum' = '127.0.0.1:2181'
); 

CREATE TABLE rtdw_db.kafka_table (
  column_two string,
  column_three string,
`PROCTIME` AS `PROCTIME`()
) WITH (
  'topic' = 'xxxx',
  'properties.group.id' = 'group_id',
  'connector' = 'kafka',
  'format' = 'json',
  'scan.startup.mode' = 'group-offsets',
  'properties.bootstrap.servers' = '127.0.0.1:9092'
); 

flink sql :

select * from rtdw_db.kafka_table  kt left join dim_db.dim_table  FOR SYSTEM_TIME AS OF kt.PROCTIME AS dt  on kt.column_two = dt.column_two ;


I want to implement such this logic which using flink sql .



---
Best,
WuKong
 
From: Jing Ge
Date: 2022-03-19 01:37
To: WuKong
CC: user@flink.apache.org
Subject: Re: Flink SQL 1.12 How to implement query Hbase table on secondary index
HI WuKong,

Afaiu, technically, you are not using Hbase secondary index(coprocessor). What you are trying to do is to store the synced dim table in elasticsearch and query from there to get the rowkeys and then use the rowkeys to get dim table rows from Hbase. In this way, a (full) table scan in Hbase will be avoided. Please correct me if I am wrong.

Would you like to share more information about your requirements in detail? like DDLs of Kafka fact table, Hbase and ES dim table and the join logic you want to achieve. So we could give you more feasible suggestions. Thanks.

Best regards,
Jing

On Thu, Mar 17, 2022 at 3:24 AM WuKong <wu...@foxmail.com> wrote:
Hi, 
    now my data store hbase and I want use flink to implement kafka table temproal join hbase table , but condtion is not rowkey ,  I realize hbase secondary index, how can I implement this function what can use flink sql first query secondary index(such as es) and then use rowkey query hbase table (must implement own hbase secondary connectors ？) 



---
Best,
WuKong

Re: Flink SQL 1.12 How to implement query Hbase table on secondary index

[Jing Ge <ji...@ververica.com>]

HI WuKong,

Afaiu, technically, you are not using Hbase secondary index(coprocessor).
What you are trying to do is to store the synced dim table in elasticsearch
and query from there to get the rowkeys and then use the rowkeys to get dim
table rows from Hbase. In this way, a (full) table scan in Hbase will be
avoided. Please correct me if I am wrong.

Would you like to share more information about your requirements in detail?
like DDLs of Kafka fact table, Hbase and ES dim table and the join logic
you want to achieve. So we could give you more feasible suggestions. Thanks.

Best regards,
Jing

On Thu, Mar 17, 2022 at 3:24 AM WuKong <wu...@foxmail.com> wrote:

> Hi,
>     now my data store hbase and I want use flink to implement kafka table
> temproal join hbase table , but condtion is not rowkey ,  I realize hbase
> secondary index, how can I implement this function what can use flink sql
> first query secondary index(such as es) and then use rowkey query hbase
> table (must implement own hbase secondary connectors ？)
>
> ------------------------------
> ---
> Best,
> WuKong
>