Ibis to Sql using ibis.tosql()

Question

I want Query to be in this format

SELECT u.name, nicknames AS nickname FROM `india.nvidia.user_table` u, UNNEST(nicknames) AS nicknames WHERE u.l is true

my py file

import ibis region='india' account='nvidia' # Create a dummy table user_profile = ibis.table({ 'name': 'string', 'nicknames':'array<string>', 'l': 'boolean' }, name=f'{region}.{account}.user_table') unnested = user_profile.mutate(nickname=user_profile.nicknames.unnest()) filtered = unnested[unnested.l == True] result = filtered[['name','nickname']] print(ibis.to_sql(result))

Not getting the exact match can someone help me out here? Trying to convert ibis to sql for better multiple execution. Anyone with the knowledge would be a great help.

Mahrez BenHamad · Accepted Answer · 2024-09-05 13:53:09Z

0

Can you try the following:

import ibis region = 'india' account = 'nvidia' user_profile = ibis.table({ 'name': 'string', 'nicknames': 'array<string>', 'l': 'boolean' }, name=f'{region}.{account}.user_table') unnested = user_profile.mutate(nickname=user_profile.nicknames.unnest()) filtered = unnested[unnested.l] result = filtered[['name', 'nickname']] sql_query = ibis.to_sql(result) print(sql_query)

answered Sep 5, 2024 at 13:53

Mahrez BenHamad

2,6641 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

numblebee Over a year ago

Output:-

SELECT "t1"."name", "t1"."nickname" FROM ( SELECT "t0"."name", "t0"."nicknames", "t0"."l", UNNEST("t0"."nicknames") AS "nickname" FROM "india.nvidia.user_table" AS "t0" ) AS "t1" WHERE "t1"."l" = TRUE

Mahrez BenHamad Over a year ago

isn't the expected output ?

numblebee Over a year ago

Nope...I think we can't unnest like the above output in BQ. unnest should be after from..Like the output I want in the question itself!

Collectives™ on Stack Overflow

Ibis to Sql using ibis.tosql()

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related