topic Re: Query table based on table_name from information_schema in Data Engineering

Query table based on table_name from information_schema

nilton — Thu, 02 May 2024 19:34:42 GMT

Hi,

I have one table that changes the name every 60 days. The name simple increases the number version, for example:
* Firtst 60 days: table_name_v1. After 60 days: table_name_v2 and so on.

What i want is to query the table wich name returned in the query of information_schema. This is important because when the name change, I won't need to adapt the query name.
Here is a example of what I want.

CREATE OR REPLACE TEMP VIEW db_copart AS

SELECT

table_name

FROM information_schema.tables

WHERE

table_schema = 'table_sandbox'

and table_name ILIKE '%db_pi_copart_v%'; -- this will return the latest table version

SELECT

FROM table_sandbox.(select table_name from db_copart)

Thank you all for any help.

Re: Query table based on table_name from information_schema

radothede — Thu, 02 May 2024 20:31:45 GMT

The simpliest way would be propably using spark.sql

%py tbl_name = 'table_v1' df = spark.sql(f'select * from {tbl_name}') display(df)

From there, You can simply create temporary view:

%py df.createOrReplaceTempView('table_act')

and query it using SQL statements:

%sql select * from table_act order by 1 asc;

Re: Query table based on table_name from information_schema

nilton — Thu, 02 May 2024 20:35:01 GMT

Thank you @radothede , but in Databricks SQL I can only use SQL, as far as I know