General thoughts

[dimitri-b]

I have spent some hours experimenting with preql and wanted to share some thoughts:

• the concept is similar to KQL language (https://github.com/microsoft/Kusto-Query-Language) which I find a lot more fluid and capable than SQL for the knid of work I do
• preql syntax allows me to perform the same data transformation steps with a lot more clarity and a lot less contortions, compared to SQL
• REPL is pretty good, but I find it awkward to use compared to something like a SQL editor or BigQuery Console UI. If I need to add a column to a projection clause at the start of a statement, I have to skip along the command backwards one word at a time, instead of just clicking there. Many times I hit Enter to indent the line (editor habit), and launched the malformed command instead. I guess Vim users will feel at home with the current REPL, I'm not a Vim user.
  It would be nice to be able to construct a script in an editor (VS Code or Pycharm) with syntax highlighting and auto-completion, and run it when ready, getting results in a console. I know it is on the roadmap, it would be a great feature to have.
• my work involves templated SQL scripts executed from Python modules, but with preql I haven't figured out an elegant way of doing that. Jinja and Python f-strings use curly braces, which are reserved by prql, I tried string.Template and it sort-of worked for me but it was a bit clumsy.

[erezsh]

Thank you for your input.

I have to skip along the command backwards one word at a time

It should be also possible to use home/end and up/down to navigate.

It would be nice to be able to construct a script in an editor

Have you tried using Jupyter Notebook? It has autocompletion (with tab), and basic syntax highlighting. The editor there isn't as capable as vscode, but perhaps it can be improved with extensions.

Another avenue I can offer is to add an "execfile()" function, that will just run the file as if it was written into the REPL. That will allow you to open the file with whatever editor you choose, and then run the edit while in the REPL.

Let me know if these aren't enough, and I'll see what else I can do to solve your flow.

my work involves templated SQL scripts executed from Python modules

Can you provide a bit more detail on the kind of templates that you use?

Preql lets you provide arguments when calling it, so you can do something like pql('a + b', a=3, b=10).

Have you considered Mako? I had a good experience with it in the past, and its syntax probably won't collide with Preql's.

[dimitri-b]

I'm on a Macbook, don't have Home/End buttons. I tried arrow keys with various modifiers, with mixed success.

I'm using Pycharm, and I can configure a command to run a shell script which will run the staticly set .pql file, so it can be used as a workaround to edit and test the script. Ideally I'd like to inject the active / current file variable into the script, but I'm not sure it is possible in Pycharm.
Also, I can tell Pycharm to treat a .pql file as JavaScript, and get some syntax highighting, but not all, and it is a hack.

I'll try Jupyter, will let you know, but we do most of our work in IDEs.

Here is a templated example that works, the $source_table variable inside the script gets substituted.
I tried the same script using arguments as suggested, but got an error, it looks like substitutions didn't happen at run time.

    SOURCE_TABLE = "bigquery-public-data.covid19_open_data.covid19_open_data"

    script_template = Template("""
        // raw data
        covid19 = import_table(
            '$source_table',
            [
                'country_code', 'country_name', 'place_id', 'location_key', 'date', 
                'new_confirmed', 'cumulative_confirmed', 'cumulative_tested'
            ]
        )
        
        // built-in round(x) doesn't take precision param
        // pres - number of decimals
        func rounded_ratio(x, y, pres) {
            mp = 10 ** pres
            return round(sum(x) / sum(y) * mp) / mp
        }
        
        // clean up, calculate ratio of positive tests, sort
        positive_ratios = covid19[cumulative_tested > 0]{
            country_code, country_name => confirmed_ratio: rounded_ratio(cumulative_confirmed, cumulative_tested, 3)
        } order {^confirmed_ratio}
        
        // get top ten
        top_ten = positive_ratios[..10]
    """)

    script = script_template.substitute(source_table=SOURCE_TABLE)
    return p(script)

[dimitri-b]

I tired a few simple things in Jupyter - it is a much better experience for people not used to CLI work.

[erezsh]

I'm glad Jupyter improves the experience. There are advantages to both, but I can definitely see why the REPL is a little challenging. I hope to fix this at some point (I'm thinking of rewriting it with a new library called Textual)

As for the templating, what I meant is that Preql has "templating" builtin. For example, this works for me:

SOURCE_TABLE = "bigquery-public-data.covid19_open_data.covid19_open_data"

script = """
    // raw data
    covid19 = import_table(
        source_table,
        [
            'country_code', 'country_name', 'place_id', 'location_key', 'date', 
            'new_confirmed', 'cumulative_confirmed', 'cumulative_tested'
        ]
    )
    
    // built-in round(x) doesn't take precision param
    // pres - number of decimals
    func rounded_ratio(x, y, pres) {
        mp = 10 ** pres
        return round(sum(x) / sum(y) * mp) / mp
    }
    
    // clean up, calculate ratio of positive tests, sort
    positive_ratios = covid19[cumulative_tested > 0]{
        country_code, country_name => confirmed_ratio: rounded_ratio(cumulative_confirmed, cumulative_tested, 3)
    } order {^confirmed_ratio}
    
    // get top ten
    top_ten = positive_ratios[..10]
"""

print(p(script, source_table=SOURCE_TABLE))

[dimitri-b]

It worked for me too, in the version installed from repo's master, not sure what happened last time.
However, the templated variable is not explicitly marked in the script, so it feels a bit weird - does preql recognise it as a variable based on the script's syntax, or just replaces that text wherever it finds it, i.e. works like template.replace?

[erezsh]

Yes, Preql recognizes it as a variable.

When you do p(script, source_table=SOURCE_TABLE), Preql inserts "source_table" as a temporary global variable, and runs the code normally.

If you want it to stand out, you can rename it to SOURCE_TABLE, or __source_table__, or anything of that sort. The convention is up to you.