In the article querying-one-trillion-rows-of-data-with-powerbi-and-azure-databricks written by Kyle Hale, he had tested combining azure databricks with Power BI sucessfully.
We followed the same steps for a table with around 1 Trillion rows:
- The table was created in a lakehouse by the following code:
from delta.tables import DeltaTable
DeltaTable.create(spark)\
.tableName("FACT_TABLE")\
.addColumn("ID", "STRING")\
.addColumn("TIC", "STRING")\
.addColumn("UPDATE_TIMESTAMP", "TIMESTAMP")\
.addColumn("SRC", "STRING")\
.addColumn("PRICE", "DOUBLE")\
.addColumn("QUANTITY", "DOUBLE")\
.addColumn("TYPE", "STRING")\
.addColumn("DATE", "DATE", generatedAlwaysAs="CAST(UPDATE_TIMESTAMP AS DATE)")\
.addColumn("HOUR", "INT", generatedAlwaysAs="HOUR(UPDATE_TIMESTAMP)")\
.addColumn("MINUTE", "INT", generatedAlwaysAs="MINUTE(UPDATE_TIMESTAMP)")\
.addColumn("SECOND", "INT", generatedAlwaysAs="SECOND(UPDATE_TIMESTAMP)")\
.partitionedBy("DATE", "HOUR")\
.execute()
- Then the table was populated by writing pyspark dataframe from a notebook.
In power bi we used Direct Query with Dynamic M parameters (Date, Hour and Minute) to try to limit the number of records being fetched from a query. For one hour we have around 30 million rows or around 0.5 million per minute when we are counting using:
df = spark.sql("SELECT * FROM LH.FACT_TABLE where date = '2023-06-10' and HOUR = 11 and minute = 12")
df.count()
from a notebook. The result is returned very quickly (less than 4 minutes).
When trying to run the same sql query from the sql endpoint of the LH, the result is never returned even after waiting for several hours. As if the SQL Query is not doing any partition pruning.
As we had chosen to connect the sql endpoint in power bi, we are unable to report on that table.
Is there anyone out there dealing with 1 Trillion row tables in power bi? If yes, were you able to report on the table, or are you facing the same problem as mine.
Thanks
