Hi, everyone! I have a specific question about dynamic pruning or query optimizer. So, suppose I have a table

A

and

B

. In both tables I have

record_date

field, for example. Now what I want is to select records from

A

, which has

record_date > max(B.record_date)

. This one works fine with Databricks:

select
    ...
from
    A
where
    A.record_date > (select max(record_date) from B)

When I see the plan - it actually tries to precompute the subquery and use the single value for

record_date

for pruning on read. But now let's suppose that we want to compute few more different attributes from

B

, to filter out the records in

A

. The absolutely equivalent declaration would be

with constants as (
    select
        max(record_date) as max_record_date,
        min(record_date) as min_record_date,
        now()::date < min(record_date) as is_some_flag
    from
        B
)
select
    ...
from
    A
    cross join constants
where
    A > max_record_date

However Spark does the nested loop join after reading everything from

A

to filter out in that case. Example on the screenshot. Does anyone know how to instruct Spark optimizer to be more smart in that case?

Hi everyone, I'm trying to run a delete in databricks as: delete from a inner join (select ) as t on a.col = t.col But I got back errors upon execution as the statement is wrong, does databricks support the sort of deletes above? thanks for confirming

Hi, How can I have white spaces in the column names while creating the table in databricks. create table dbname.tablename (

First Account

varchar(50),

Second Account

varchar(50) ) It returns error: contains invalid character(s) among ” ,;{}()\n\t=“. Please use alias to rename it. If I have similar column names, it would be easier for me to write down the data to SQL Server in a straightforward way!

is it possible add a new partition to a current delta table in databricks?

Has anyone tried predictive I/O or done any benchmarks recently? It seems promising, going to test this week to see how much reduction on merge and read this week.

Hello everyone, I have a question related to the ability of applying DDL's on our delta lake catalogs/schemas objects in databricks. I'm looking for a tools like DACPAC (https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver16) to automate these changes on the tables from the CI/CD pipelines. any thoughts? thanks

Hi guys. I am trying to convert tables columns names upper case to lower case . For that i am using below code. for table in table_list:     spark.sql(f'''ALTER TABLE {table}         SET TBLPROPERTIES (         'delta.columnMapping.mode' = 'name',         'delta.minReaderVersion' = '1',         'delta.minWriterVersion' = '2')''')     print(table)     col_list=spark.table(table).columns     if len(col_list) > 0 :         for col in col_list:             spark.sql(f'''ALTER TABLE {table} RENAME COLUMN {col} to {col+'_tmp'} ''')             spark.sql(f'''ALTER TABLE {table} RENAME COLUMN {col+'_tmp'} to {col.lower()} ''') I am getting below error in sometimes if i rerun its working fine. AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: at least one column must be specified for the table Can anyone help me out here why i am getting above error. Thanks

Hi Everyone, I am trying to dispaly dataframe which have 20 rows.but i am getting below error. com.databricks.rpc.RPCResponseTooLarge: rpc response (of 20979958 bytes) exceeds limit of 20971520 bytes Can anyone please help here to fix this issue. Thanks