cancel
Showing results for 
Search instead for 
Did you mean: 

How to Pull Cached SQL Table into Python Dictionary?

drewtoby
New Contributor II

Hello,

I have been working on this issue as a proof of concept - it would be extremely helpful to iterate through tables via loops in a few scenarios. I have a simple three column dimension that I added to a cached table.

cache lazy table hedis_cache select * from hofhc.hedis_dim

I then tried the following two methods. The first is coming up empty, whereas the second is returning data as a dataframe, not a python dictionary.

Method 1 

Method 2 

Any advice? Thanks in advance for all the help!

1 ACCEPTED SOLUTION

Accepted Solutions

daniel_sahal
Honored Contributor III

@Andrew Begg​ 

First of all, cache table creates a view, not table, so you won't be able to use pd.read_table (https://docs.databricks.com/sql/language-manual/sql-ref-syntax-aux-cache-cache-table.html).

About the second method - .select() still gives a dataframe as an output. You need to find a way to convert DF to dict.

View solution in original post

2 REPLIES 2

daniel_sahal
Honored Contributor III

@Andrew Begg​ 

First of all, cache table creates a view, not table, so you won't be able to use pd.read_table (https://docs.databricks.com/sql/language-manual/sql-ref-syntax-aux-cache-cache-table.html).

About the second method - .select() still gives a dataframe as an output. You need to find a way to convert DF to dict.

drewtoby
New Contributor II

Got it to work, thank you for the tip! I needed to convert the dataframe over to a pandas dataframe

https://www.geeksforgeeks.org/convert-pyspark-dataframe-to-dictionary-in-python/

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.