Allow querying DataFrame directly in sql method

[floscha]

While I really like the idea of @rxin's recent #256 PR, he uses an (in my opinion) over-simplistic example of ks.sql("select * from range(10) where id > 7"). I believe that the ability to query actual Koalas DataFrames through SQL can prove really valuable to many users. However, when trying to use ks.sql with a Koalas DataFrame, the following exception occurs:

kdf = ks.DataFrame({'A': [1,2,3]})
>>> ks.sql("select * from kdf")
...
org.apache.spark.sql.AnalysisException: Table or view not found: kdf; line 1 pos 14
...

This is not surprising to someone with PySpark knowledge who knows that kdf has to be registered as a temporary table before being able to use it with SparkSQL. Unfortunately, (as I understand it) the target group of the Koalas library should not be expected to be Spark experts. To get the above example working, the following workaround is needed, which requires the usage of the lower-level (Py)Spark API, thus somewhat defeating the purpose of Koalas.

>>> from pyspark import SparkContext
>>> from pyspark.sql import SQLContext
>>> sc = SparkContext.getOrCreate()
>>> sql = SQLContext(sc)
>>> sql.registerDataFrameAsTable(kdf._sdf, "kdf")
>>> ks.sql("select * from kdf")
   __index_level_0__  A                                                         
0                  0  1
1                  1  2
2                  2  3
# Optionally clean-up by dropping the temporary table
>>> sql.dropTempTable("kdf")

Wouldn't it be much more convenient if this "temporary table magic" would instead be handled by Koalas behind the scenes or are there any design objections against such an approach?

[HyukjinKwon]

I think we shouldn't mix PySpark APIs and Koalas APIs. Maybe we should add a method called to_spark(..) and delegate everything to there.

As you said, I think ks.sql's purpose is mainly to generate some data or read data from Spark source only. We maybe should document this limitation clearly.

So, when they want to switch to PySpark context, they should explicitly call to_spark(...). Once we start to mix both, it might be difficult for Koalas to target its original goal, Pandas corresponding APIs.

[floscha]

I see the point of not mixing the two libraries' APIs. Then again, how does ks.sql relate to the pandas API where no pandas.sql() exists?

Also, the mentioned to_spark() method already exists but I don't quite understand how it relates to the ks.sql method.

[HyukjinKwon]

how does ks.sql relate to the pandas API where no pandas.sql() exists?

I was thinking adding some APIs non-existent in Pandas (with some strong reasons) might make sense (see https://github.com/databricks/koalas#unify-small-data-pandas-api-and-big-data-spark-api-but-pandas-first); however, if that brings a bunch of compatibility issues, I think we should restrict its usage rather than putting efforts to extend PySpark's API usage.

Also, the mentioned to_spark() method already exists but I don't quite understand how it relates to the ks.sql method.

For instance, sql.registerDataFrameAsTable(kdf.to_spark(), "kdf") will work instead. We could additionally consider like a flag that drops index simply because PySpark users are not familiar with it anyway.

[thunterdb]

@floscha it is interesting that you mention that topic now. I was looking into how to port pandasql to koalas, taking into account python's normal syntax for string interpolation. As an example, this statement:

date = pd.Timestamp('2012-05-01')
ids = [1,2,3]
kdf = ks.read_csv(...)
filtered_kdf = ks.sql(""" SELECT m.field1, m.field2 FROM {filtered_kdf}  m WHERE m.date >= {date} and m.ids in {ids} """)

This is equivalent to:

kdf.asDF().registerTempView("_koalas_tmp_1")
filtered_kdf = ks.sql(""" SELECT m.field1, m.field2 FROM _koalas_tmp_1  m WHERE m.date >= '2012-...' and m.ids in (1,2,3) """)

The variables are not necessary as a start, but they are very convenient because I see a lot of error when doing string interpolation in general. The pandasql uses a regex that seems pretty good to get started with to extract the variables.

This syntax has the advantage that it is safe from injection attacks and it is familiar to any python user (it is the same as string.format()).

What are the thoughts about this design?

[thunterdb]

I forgot the link: https://github.com/yhat/pandasql
They choose to not up brackets. The advantage of using brackets is that any python user will be familiar with the format, the IDE will probably have autocomplete (I see that github make the proper highlights) and it allows the interpolations of arbitrary values.

[floscha]

Hey @thunterdb, I also had pandasql in the back of my head but wasn't quite sure whether or not you consider it to be "actual" pandas. Injecting values from variables is definitely a cool feature to have but I think first of all there needs to be a decision if the functionality to run SQL queries should be part of Koalas or rather something external like KoalaSQL 😉

@HyukjinKwon I don't really see compatibility issues since there is no pandas.sql() method that could cause name conflicts. On the other hand, as soon as users start creating Spark/SQLContexts and temporary tables, I believe they are comfortable enough to use PySpark instead of Koalas anyway. So having code like sql.registerDataFrameAsTable(kdf.to_spark(), "kdf") seems quite counter-intuitive to me.

[HyukjinKwon]

@floscha, what I mean by compatibility is between Koalas DataFrame and Spark DataFrame. Now we have to consider compatibility with Spark SQL <> Koalas DataFrame as well.

On the other hand, as soon as users start creating Spark/SQLContexts and temporary tables, I believe they are comfortable enough to use PySpark instead of Koalas anyway.

Yes, in that case, we should rather avoid to add Spark related APIs since we target users who want to use something different from PySpark. Proper fence is needed to distinguish sometimes.

[floscha]

@HyukjinKwon, what compatibility between Spark SQL and Koalas DataFrame do you mean? The project already has several dependencies on Spark SQL that require compatibility in some sense.

And again, ks.sql() is already a method that has no equivalent in pandas but rather directly translates to PySpark's DataFrame.sql() method. Since it defines itself as Execute a SQL query and return the result as a Koalas DataFrame., I'd say the only two consistent options would be to also allow using Koalas DataFrames in such SQL queries or to drop the method altogether.

Maybe @rxin also has an opinion on this since he introduced the ks.sql() method in #256.

[rxin]

Great discussion. Here are my thoughts: my vision is that there is a nontrivial chance Koalas becomes the de facto standard "big data science" API over time.

That is, when data scientists work with big data, they use Koalas. They know it runs on Spark (or maybe even some other thing in the future), but they don't necessarily know the PySpark API because that's all they have learned (when they transition from pandas over to Koalas).

To accomplish that, it's necessary to make sure from a functionality point of view, Koalas API is a superset of PySpark's (at least when it comes to the structured APIs; I'd not consider RDD or MLlib here).

A lot of production data sits in data lakes (S3, Azure blob store, HDFS, etc). If a data scientist wants to work with some production data, the first thing he/she will likely do is to directly load in that path, table, or run a SQL query. In this context, I think it's necessary to have the "sql" function.

Similarly, in order to work with big data, some additional functionalities that are unique to PySpark but not pandas are critical, e.g. "cache", "repartition", even though some data scientists will never use that.

One thing you could argue is that since Koalas runs on top of Spark, and it is trivial to convert between Spark DataFrames to Koalas DataFrames, why not just leave all the Spark specific functions in Spark and have users fall back to those?

To answer that question, we should consider the journey of data scientists. Let's use "Alice" as an example:

1. Alice learns how to do data science using pandas from MOOCs
2. Alice works on some real world data science problems with pandas
3. Alice needs to work with some large production dataset in S3. Before Koalas (is mature/popular), she learns that PySpark is the thing to use and she now needs to learn a brand new API, which is daunting. After Koalas (is mature/popular), Alice reads some quick start guide on Koalas and then can immediately start applying most of what she learned from pandas to Koalas.
4. Alice runs into some trouble that are specific to big data. For example, some operation that used to be super fast on tiny amount of data is now super slow on large amount of data. She reads up on how to address those issues. Hypothetically speaking, let's say Alice's more senior coworker tells her she just needs to cache the data to make the workload go faster.

At this point, which one do you think is easier for Alice?

Option 1. Go research and figure out how to do cache. Alice hasn't really been exposed to any of the Spark API yet, so even reading up the Spark API reference doc is daunting.

Option 2. Alice tries to do df.ca and tab autocomplete suggests "cache", which seems like what she wants to do. She confirms that by looking at the reference doc.

My theory is that it would be significantly easier for Alice and other data scientists to just learn from one place and learn one API. And this doesn't stop at just performance optimizations. Accessing data, plotting, ...

Along that direction, I do think it will be super convenient for data scientists to just run SQL queries in Koalas against Koalas DataFrames, without having to learn about registerTempView. After all, the main objective of the Koalas API should be to reduce friction and make data scientists more productive.

[tdliu]

Those are really good points...
In any case, making the overall goal clear is important. Summarizing the discussion: we can go in two directions.

1. Make Koalas mirror pandas as much as possible 1:1. In this scenario, we only implement the gaps in making pandas work on big data. Users familiar with Python DS ecosystem will be able to migrate single node workloads with minimal changes. Also crucially, they can migrate work back to single node trivially. However as popular as pandas is, it still has its quirks. It is not trivial for SQL or R users to pick up.
2. Build the standard big data API, where we augment pandas. We implement pandas' methods and are careful not to deviate. As we've seen from PySpark, slight differences can really trip up users. Instead of just copying pandas, however, we are user-driven. There are many more users who know SQL than pandas for instance. Combining the two could be extremely powerful - more powerful than pandas. The tradeoff is that moving from pandas to Koalas is no longer bidirectional.

I think the bidirectional use case is actually really important. Within an organization, there are many patterns for working with big data. For example, many developers still have a strong preference for sampling data and developing locally. If Koalas and pandas are not 1:1, there could be issues with handoffs.

[rxin]

@tdliu they could run Koalas locally with Spark though :) No setup needed.

[rxin]

BTW on this specific function ... I can think of three ways:

1. Be more "magical" and just automatically import all locals() and globals() DataFrames, and create temporary views for them. This would be by far the most convenient and fairly error prone. So the following will work out of the box:

my_range = ks.range(1000)
ks.sql("select count(*) from my_range")

2. Similar to pandasql, require explicitly passing in the list of DataFrames. The above will now look like this:

my_range = ks.range(1000)
ks.sql("select count(*) from my_range", locals())

Users can accomplish option 1 by defining a method:

sql = lambda q: ks.sql(q, globals())

3. Use the string interpolation @thunterdb outlined above, e.g.

my_range = ks.range(1000)
ks.sql("select count(*) from {my_range}")

[floscha]

Hi @rxin, thanks for your extensive thoughts on the topic. Here is my opinion on the suggested approaches:

1. I would definitely prefer this version of the ks.sql() API since it directly translates to common SQL which I assume is what users would expect. Also, it would be consistent with Spark SQL's sql() method. However, I strongly dislike creating temporary views for all DataFrames out of which the majority might never be used. While this would work for a first proof of concept, in the long term I think it would be cleaner to scan the query for potential DataFrame names (e.g. whatever comes after the from statement) and only create temporary views for those DataFrames. Additionally, the use of locals() and globals(), which would be necessary in this case, can quickly cause problems once users split their code across several Python modules.
2. Personally, that's the option I like the least since it combines a somewhat awkward API (does the majority of users even understand what locals() is?) with the same technical issues as above by potentially creating temporary views for all DataFrames. Also, the lambda statement only makes it more complicated in my opinion (remember the case where analysts simply want to run a SQL query instead of getting into software development).
3. What I like about this approach is that it seems the cleanest from a technical perspective. However, it introduces a kind of new SQL dialect which (while easy to learn) would surely confuse most users at first. Also, as described above, I feel like the same could be achieved by parsing the query to find the involved DataFrame names without explicitly marking them with curly brackets.

[floscha]

On the more general vision of Koalas:

• I feel like the priority should be on meeting the expectations of users coming from a pandas background. Augmenting the pandas API with Spark-specific functionality like .cache(), or .sql() for that matter, would therefore not be an issue as long as no pandas methods with the same name exist.
• A valid objection would be not being able to move back from Koalas to pandas. However, it should be assessed how common this case actually is since I believe people do much rather move from small data to big data than the other way around. Also, as pointed out by @rxin, it is still perfectly possible to run Koalas on any edge device with a local Spark installation. At last, for Koalas to be feasible in real-world use cases, I argue that it is essential to support at least a small subset of Spark-specific methods like .cache() without which Koalas would rather be some nice tech demo than anything usable in practice. So while it would be great to have full API compatibility in theory, I don't see how this could be achieved in practice and, as described above, it might not even be necessary.