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.
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)
3