Support json_tuple in SELECT clause in Hive

[Light-Towers]

Describe the bug

• No dialect parameters are normal. Use Hive dialect will error.

SQL
Paste the SQL text here. For example:

INSERT OVERWRITE table dw.dim_dict partition (dt)
select get_json_object(replace(t.id, '$', ''), '$.oid') as id,
       t.industry_code,
       t.industry,
       t.sec_industry_code,
       t.sec_industry,
       t.type,
       t.version,
       current_date                                     as dt
from (select json_tuple(line, '_id', 'industryCode', 'industry', 'secIndustryCode', 'secIndustry', 'type', 'version')
                 as (id, industry_code, industry, sec_industry_code, sec_industry, type, version)
      from default.tmp_json) t;

To Reproduce

# -*- coding: utf-8 -*-
from sqllineage.runner import LineageRunner

def test_create_as():
    sql = """
INSERT OVERWRITE table dw.dim_dict partition (dt)
select get_json_object(replace(t.id, '$', ''), '$.oid') as id,
       t.industry_code,
       t.industry,
       t.sec_industry_code,
       t.sec_industry,
       t.type,
       t.version,
       current_date                                     as dt
from (select json_tuple(line, '_id', 'industryCode', 'industry', 'secIndustryCode', 'secIndustry', 'type', 'version')
                 as (id, industry_code, industry, sec_industry_code, sec_industry, type, version)
      from default.tmp_json) t;
    """
    # result = LineageRunner(sql)
    result = LineageRunner(sql, 'hive')
    result.print_column_lineage()
    print(result.source_tables)
    print(result.target_tables)

if __name__ == "__main__":
    test_create_as()

Line 11, Position 18: Found unparsable section: 'as (id, industry_code, industry, sec_ind...'

Python version (available via python --version)

• 3.10.11

SQLLineage version (available via sqllineage --version):

• 1.4.8

[reata]

Can you confirm this SQL is executable and share the Hive version you're using?

Based on Hive Doc, JSON_TUPLE must be used together with LATERAL VIEW.

On the other hand, SparkSQL Doc does explicitly say they support JSON_TUPLE used in SELECT clause. And this SQL is parsable if you specify dialect=sparksql.

[Light-Towers]

Thank you for your reply !

My hive version: 3.1.3
I use JSON_TUPLE without LATERAL VIEW successful 😂 ， Maybe I should use official recommended grammar.

[reata]

Thanks for the info. Since it's executable, I'll follow up with sqlfluff community to get this syntax supported in Hive dialect.

[reata]

This is the same issue as sqlfluff/sqlfluff#3160, I'll try create a PR for that.

[reata]

Upstream PR sqlfluff/sqlfluff#5495 is merged. We will wait for next release of sqlfluff, and upgrade the dependency version, this syntax will be supported then.

[reata]

Close as complete of #593