topic Re: Notebook's Widget parameters in SQL cell => howto in Data Engineering

Notebook's Widget parameters in SQL cell => howto

yopbibo — Tue, 24 May 2022 09:18:12 GMT

dbutils.widgets.text('table', 'product')
 
%sql
select *
from ds_data.$table

Hello, the above will work.

But how can I do something like:

dbutils.widgets.text('table', 'product')
 
%sql
select *
from ds_data.$table_v3

in that example, $table is still my table name, and I want to add a suffix.

I could go in python

dbutils.widgets.text('table', 'product')
tab = dbutils.widgets.get('table')
spark.sql(f'select * from ds_data.{tab}_v3')

but would love doing it in SQL.

Any idea?

thanks!

Re: Notebook's Widget parameters in SQL cell => howto

Prabakar — Tue, 24 May 2022 11:44:20 GMT

hello @Philippe CRAVE you can use something like this and it should work.

%sql
select * from prabakar.`$table`_v1

Re: Notebook's Widget parameters in SQL cell => howto

yopbibo — Tue, 24 May 2022 12:01:13 GMT

Thanks!

Actually, did not work for me 😞

this:

%sql
select * from prabakar.`$table`_v1

would give:

Error in SQL statement: AnalysisException: Table or view not found: prabakar.table_name;

seems like missing the "_v1" anyway

Re: Notebook's Widget parameters in SQL cell => howto

yopbibo — Tue, 24 May 2022 12:02:31 GMT

Maybe I should add that I use DB9.1 on a high concurrency cluster

Re: Notebook's Widget parameters in SQL cell => howto

Prabakar — Tue, 24 May 2022 15:31:59 GMT

looks like you are using the example that I share `Table or view not found: prabakar.table_name;` Can you test with your sample data.

Re: Notebook's Widget parameters in SQL cell => howto

yopbibo — Tue, 24 May 2022 15:34:10 GMT

I tried using my sample data, translated the message to match yours

Re: Notebook's Widget parameters in SQL cell => howto

Prabakar — Tue, 24 May 2022 15:36:07 GMT

I checked further and found that the command that I used is not helpful. Though it takes the table name, it is pulling data from the agents table but not the agents_v1 table. The agents table has 12 rows and agent_v1 table has 20 rows. So this doesn't work. We need to programmatically call the table with the suffix. I don't think its possible with sql.

Re: Notebook's Widget parameters in SQL cell => howto

yopbibo — Tue, 24 May 2022 15:39:13 GMT

ok, thanks. Sad, forces us to move to python notebooks for something that was fitting very well a sql notebook. but that's life 🙂

thanks!

Re: Notebook's Widget parameters in SQL cell => howto

Prabakar — Tue, 24 May 2022 16:13:53 GMT

🙂