Modify From Clause In Custom Dialect Question

[cgivre]

Hello all,
First of all, thank you very much for this really amazing module!!

I am interested in writing (and contributing) a dialect for Apache Drill. Drill uses ANSI SQL (2003) with some additions so this looks fairly straightforward. However, there is one piece which I'm not quite sure how to handle.

Drill allows the user to change certain options at query time. For example, if you are querying an Excel file and want to query something other than the default sheet, you could write a query like this:

SELECT <fields>
FROM table( dfs.`test_data.xlsx` (type => 'excel', sheetName => 'secondSheet'))

My goal here is to get the Drill dialect to recognize this as valid SQL and basically leave it alone. Are there any examples of something similar or do you have any suggestions?
Thanks!!

@tobymao Posted the following response on an issue I created:

this is an easy change, you just need to make drill accept lambda / kwargs

python -m sqlglot "select * from y(w(x -> 'z'))"
SELECT
  *
FROM Y(W("x" -> 'z'))

already works, so it's just a matter of adding a mapping of => to -> or a new thing

Are there any examples of this?

[tobymao]

yes, look at snowflake, it maps -> to lambda

[tobymao]

this commit makes it so all dialects now support => as a kwarg operator

fda1168

[tobymao]

in order to get your query to work you need to also parse

from dfs.test.xlsx()

this requires the func parser that is done in _parse_column like

            else:
                field = self._parse_star() or self._parse_function() or self._parse_id_var()

            if isinstance(field, exp.Func):
                # bigquery allows function calls like x.y.count(...)
                # SAFE.SUBSTR(...)
                # https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference#function_call_rules
                this = self._replace_columns_with_dots(this)

this is a bit tricky, to do for a first timer, so i can help you out if you explain to me what

dfs.test_data.xlsx is

[cgivre]

@tobymao Thanks for all your help so far. Firstly, here's a link to my branch with he Drill dialect 1.

Here is an answer to your question. Forgive me if this is too long.

TL;DR

dfs.test_data.xlsx is the data source. Dfs represents the local file system and test_data.xlsx is the name of the file to be queried.

Longer Explanation

Let me explain a bit about what the table() function does and how it works. Firstly, if you're not familiar with Apache Drill, it is an MPP Federated Query Engine similar to Presto or Impala. Drill however is a bit more of a "Swiss army chainsaw" for data, and can query many different data formats as well as systems. One of Drill's big features is that unlike the other query engines, it can do automatic schema discovery on the fly. This is both a blessing and a curse. From the user's perspective, when it works well, it is really nice because you can just point Drill at data and query it as if it was in a database.

So let's say that you have an Excel file that you want to query using Drill. Out of the box, you could write a query like this:

SELECT * FROM dfs.`/path/to/file/data.xlsx`

In this case, the dfs is essentially the catalog name. In Drill-speak, this is the name of the storage system where the data can be found. This is arbitrary and can be set by the user. DFS is usually used to indicate a local file system., however it could be an S3 bucket, Azure or whatever. The rest is simply a path to the file on that system that you want to query. Now, Drill also allows you to to define workspaces in file systems which are essentially shortcuts to paths. So instead of

FROM dfs.`/a/really/long/path/to/a/file.xlsx`

Drill lets you define a workspace so you don't have to type all that. Thus you could have a query like this:

SELECT *
FROM dfs.my_workspace.`file.xlsx`

The Table Function

With all that said, there are situations where a user will want to supply additional information to Drill at query time. The example I gave originally was that a user might want to access other sheets in an excel file, but users can also use the table() function to provide a schema. 2 There are other examples as well.

SELECT * 
FROM table(dfs.tmp.`text_table`(
schema => 'inline=(col1 date properties {`drill.format` = `yyyy-MM-dd`}) properties {`drill.strict` = `false`}'))

I hope this makes sense. I'm going to try to finish up the date/time conversions in the Drill dialect today or by Monday at the latest. The module's structure is very well engineered so I have to say writing the dialect is pretty straightforward, even for a n00b. :-)