Sylvain Hazard

    Sylvain Hazard

    9 months ago
    Hi ! I'm having a weird issue with flow registration where it looks like Prefect tries to parse
    .sql
    files as if they were
    .py
    scripts. Explanation and relevant code below.
    My project architecture looks pretty much like this :
    /src
      /projects
         /project_1
            /flows
              /some_flow
                /data
                  script1.sql
                /tasks
                  sometask.py
                flow.py
    I am using recursive glob to register all of my flows with the following bash :
    #!/bin/bash
    
    export PYTHONPATH=$(pwd):$PYTHONPATH
    
    for folder in $(ls -d ./src/projects/*/ | grep -v '__'); do
        project_name=$(basename $folder)
        prefect create project $project_name
        prefect register --project $project_name --path "${folder}**/*"
    done
    It worked great with other flows, even some that had
    .txt
    files as data. However, it looks like Prefect tries to parse the SQL scripts as if they were python :
    Collecting flows...
    Error loading './src/projects/source_updater/flows/theriaque/data/excipients.sql':
      Traceback (most recent call last):
        File "/home/sylvain/.pyenv/versions/3.8.12/envs/prefect/lib/python3.8/site-packages/prefect/cli/build_register.py", line 134, in load_flows_from_script
        namespace = runpy.run_path(abs_path, run_name="<flow>")
        File "/home/sylvain/.pyenv/versions/3.8.12/lib/python3.8/runpy.py", line 264, in run_path
        code, fname = _get_code_from_file(run_name, path_name)
        File "/home/sylvain/.pyenv/versions/3.8.12/lib/python3.8/runpy.py", line 239, in _get_code_from_file
        code = compile(f.read(), fname, 'exec')
        File "/home/sylvain/workspace/prefect-source-updater/src/projects/source_updater/flows/theriaque/data/excipients.sql", line 2
          USE theriaque;
              ^
      SyntaxError: invalid syntax
    Is there something specific in the registration method that has any effect on SQL scripts ?
    Using
    ${folder]***/**.py
    in the registration script seems to have fixed the issue. I still don't understand why there was no problem with
    .txt
    files but
    .sql
    caused some though 😅
    Anna Geller

    Anna Geller

    9 months ago
    Nice work finding a fix @Sylvain Hazard. My feeling is that it probably depends on storage and finding proper paths - do you use Docker storage?
    Sylvain Hazard

    Sylvain Hazard

    9 months ago
    I'm testing locally for now, using Local storage
    Anna Geller

    Anna Geller

    9 months ago
    probably that’s why. I created a SQL file and try to register it - I’m getting the same error:
    prefect register --project p -p tmp.sql
    Collecting flows...
    Error loading 'tmp.sql':
      Traceback (most recent call last):
        File "/Users/anna/opt/anaconda3/envs/prefect-dbt-cloud/lib/python3.9/site-packages/prefect/cli/build_register.py", line 134, in load_flows_from_script
        namespace = runpy.run_path(abs_path, run_name="<flow>")
        File "/Users/anna/opt/anaconda3/envs/prefect-dbt-cloud/lib/python3.9/runpy.py", line 267, in run_path
        code, fname = _get_code_from_file(run_name, path_name)
        File "/Users/anna/opt/anaconda3/envs/prefect-dbt-cloud/lib/python3.9/runpy.py", line 242, in _get_code_from_file
        code = compile(f.read(), fname, 'exec')
        File "/Users/anna/repos/prefect-dbt-cloud/tmp.sql", line 1
          USE theriaque;
              ^
      SyntaxError: invalid syntax
    So it looks like your bash script also tries to register .sql files. Perhaps you can grep .py files more explicitly there?
    Sylvain Hazard

    Sylvain Hazard

    9 months ago
    Well the path glob seems to allow for specific file extensions with a wildcard name, which looks like it is enough.
    Thanks for the help anyway 🙂