从示例json生成looker视图和探索的工具
j2v的Python项目详细描述
json到looker视图(j2v)
j2v是一个简单的命令行工具,可以将json转换成Looker Views和Looker Explores格式的Looker可读文件。
它还输出一个带有正确路径和爆炸表达式的sql。
这对于与关注读模式的数据库结合使用非常有用,数据存储在原始json中,而不是分解成表或视图的列。
示例用例
数据库中有一个表。此表包含包含json的列(每行一个json)。您非常好奇这些数据看起来是如何被分解的,但是您不想花费2小时来遍历json结构并指定所有字段,只是为了在looker中显示它们。
使用j2v,所有结构都会自动发现,并生成两个文件:looker视图和looker explore。您只需将此命令行工具的输出复制/粘贴到looker项目中,就可以开始探索。
用法
要求
Python 3必须安装。
如何运行
- 使用github或 中的代码
pip install j2v
参数
json_files
:json格式的文件,表示存储在表中的数据output_view
:要创建的looker视图输出文件的名称output_explore
:要创建的looker模型输出文件的名称sql_table_name
:要使用的数据库表的名称(这只在lookml文件中使用;作为此工具的一部分,不会与数据库进行实际连接)columnn_name
:db表中在sql_table_name
中指定的列的名称。(这仅在lookml文件中使用;作为此工具的一部分,不会与数据库进行实际连接)
输出
output_view
:包含looker视图定义的文件(请参见存储库中的examples目录)output_explore
:包含looker定义的文件explore exploding the structures(请参见存储库中的examples目录)
示例用法
使用所有参数
python3 main.py --json_files data1.json data2.json --output_view restaurant_chain.view --output_explore restaurant_chain.lkml --columnn_name raw_data --sql_table_name chains
仅使用强制参数
python3 main.py --json_files order_example.json order_example2.json order_example3.json
贡献
项目结构:
j2v
-包的源代码examples
-工作示例tests
-测试
贡献
- 如果不确定,请打开一个问题进行讨论
- 创建叉子
- 进行更改
- 发出拉取请求
- 快乐的贡献!
示例
输入:
{"apiVersion":"v3.4","data Provider":"Eat me","restaurants":[{"name":"Super Burger","city":"Sydney","country":"Australia","address":"Big Street 3","currency":"AUD","menu":[{"dishName":"BurgerPlus","price":10,"indegrients":["Meat","Cheese","Bun"]}]}],"headquater":{"employees":36,"city":"Olsztyn","country":"Poland","building":{"address":"3 Maja 10","floors":[1,2,7]}},"dataGenerationTimestamp":"2019-03-30T11:30:00.812Z","payloadPrimaryKeyValue":"3ab21b54-22d6-473c-b055-4430f8927d4c"}
输出:
SQL输出(现在只支持雪花方言):
SELECT---chains_table Informationchains_table."raw_data_column":"apiVersion"::stringASAPIVERSION,chains_table."raw_data_column":"data Provider"::stringASDATA_PROVIDER,chains_table."raw_data_column":"dataGenerationTimestamp"::stringASDATAGENERATIONTIMESTAMP,chains_table."raw_data_column":"headquater":"building":"address"::stringASHEADQUATER_BUILDING_ADDRESS,chains_table."raw_data_column":"headquater":"city"::stringASHEADQUATER_CITY,chains_table."raw_data_column":"headquater":"country"::stringASHEADQUATER_COUNTRY,chains_table."raw_data_column":"headquater":"employees"::numberASHEADQUATER_EMPLOYEES,chains_table."raw_data_column":"payloadPrimaryKeyValue"::stringASPAYLOADPRIMARYKEYVALUE,---restaurants Informationrestaurants.VALUE:"address"::stringASRESTAURANTS_ADDRESS,restaurants.VALUE:"city"::stringASRESTAURANTS_CITY,restaurants.VALUE:"country"::stringASRESTAURANTS_COUNTRY,restaurants.VALUE:"currency"::stringASRESTAURANTS_CURRENCY,restaurants.VALUE:"name"::stringASRESTAURANTS_NAME,---restaurants_menu Informationrestaurants_menu.VALUE:"dishName"::stringASRESTAURANTS_MENU_DISHNAME,restaurants_menu.VALUE:"price"::numberASRESTAURANTS_MENU_PRICE,---restaurants_menu_indegrients Informationrestaurants_menu_indegrients.VALUE::stringASRESTAURANTS_MENU_INDEGRIENTS_VALUE,---headquater_building_floors Informationheadquater_building_floors.VALUE::numberASHEADQUATER_BUILDING_FLOORS_VALUEFROMchains_table,LATERALFLATTEN(OUTER=>TRUE,INPUT=>chains_table."raw_data_column":"restaurants")restaurants,LATERALFLATTEN(OUTER=>TRUE,INPUT=>restaurants.VALUE:"menu")restaurants_menu,LATERALFLATTEN(OUTER=>TRUE,INPUT=>restaurants_menu.VALUE:"indegrients")restaurants_menu_indegrients,LATERALFLATTEN(OUTER=>TRUE,INPUT=>chains_table."raw_data_column":"headquater":"building":"floors")headquater_building_floors输出文件:
查看文件:
view: chains_table { sql_table_name: chains_table ;; dimension: city { description: "City" type: string sql: ${TABLE}."raw_data_column":"headquater":"city"::string ;; } dimension: provider { description: "Provider" type: string sql: ${TABLE}."raw_data_column":"data Provider"::string ;; } dimension: building_address { description: "Building Address" type: string sql: ${TABLE}."raw_data_column":"headquater":"building":"address"::string ;; } dimension: payload_primary_key_value { description: "Payload Primary Key Value" type: string sql: ${TABLE}."raw_data_column":"payloadPrimaryKeyValue"::string ;; } dimension: data_generation_timestamp { description: "Data Generation Timestamp" type: date_time sql: ${TABLE}."raw_data_column":"dataGenerationTimestamp"::string ;; } dimension: api_version { description: "Api Version" type: string sql: ${TABLE}."raw_data_column":"apiVersion"::string ;; } dimension: employees { description: "Employees" type: number sql: ${TABLE}."raw_data_column":"headquater":"employees"::number ;; } dimension: country { description: "Country" type: string sql: ${TABLE}."raw_data_column":"headquater":"country"::string ;; } } view: restaurants { dimension: country { description: "Country" type: string sql: ${TABLE}.VALUE:"country"::string ;; } dimension: address { description: "Address" type: string sql: ${TABLE}.VALUE:"address"::string ;; } dimension: currency { description: "Currency" type: string sql: ${TABLE}.VALUE:"currency"::string ;; } dimension: name { description: "Name" type: string sql: ${TABLE}.VALUE:"name"::string ;; } dimension: city { description: "City" type: string sql: ${TABLE}.VALUE:"city"::string ;; } } view: restaurants_menu { dimension: menu_price { description: "Menu Price" type: number sql: ${TABLE}.VALUE:"price"::number ;; } dimension: menu_dish_name { description: "Menu Dish Name" type: string sql: ${TABLE}.VALUE:"dishName"::string ;; } } view: restaurants_menu_indegrients { dimension: menu_indegrients_value { description: "Menu Indegrients Value" type: string sql: ${TABLE}.VALUE::string ;; } } view: headquater_building_floors { dimension: building_floors_value { description: "Building Floors Value" type: number sql: ${TABLE}.VALUE::number ;; } }
浏览文件:
include: "restaurant_chain.view" explore: chains_table { view_name: chains_table from: chains_table label: "chains_table explore" description: "chains_table explore" join: restaurants { from: restaurants sql:,LATERAL FLATTEN(OUTER => TRUE, INPUT => chains_table."raw_data_column":"restaurants") restaurants;; relationship: one_to_many } join: restaurants_menu { from: restaurants_menu sql:,LATERAL FLATTEN(OUTER => TRUE, INPUT => restaurants.VALUE:"menu") restaurants_menu;; relationship: one_to_many required_joins: [restaurants] } join: restaurants_menu_indegrients { from: restaurants_menu_indegrients sql:,LATERAL FLATTEN(OUTER => TRUE, INPUT => restaurants_menu.VALUE:"indegrients") restaurants_menu_indegrients;; relationship: one_to_many required_joins: [restaurants_menu] } join: headquater_building_floors { from: headquater_building_floors sql:,LATERAL FLATTEN(OUTER => TRUE, INPUT => chains_table."raw_data_column":"headquater":"building":"floors") headquater_building_floors;; relationship: one_to_many } }