https://prefect.io logo
Title
j

Joe Schueller

09/27/2022, 3:58 PM
Hi everyone. I'm hoping someone can enlighten me on this - I'm thinking I'm missing something obvious but not seeing it. I'm utilizing the cli commands from
prefect-dbt
and can successfully execute a flow using the
trigger_dbt_cli_command
task, but dbt doesn't actually do anything.
1
j

Jeff Hale

09/27/2022, 5:54 PM
Hi Joe. Sorry to hear about the issue. Are you able to please move the code and traceback to this thread to help keep the main channel tidy?
j

Joe Schueller

09/27/2022, 6:23 PM
sure thing
:thank-you: 1
Here's how my .py looks right now. (the airbyte portion is working just fine!)
from prefect import flow
from prefect_airbyte.connections import trigger_sync
from prefect_dbt.cli.commands import trigger_dbt_cli_command

@flow(name='Airbyte')
def run_airbyte_syncs():
    airbyte_sync = trigger_sync (
        airbyte_server_host='localhost',
        airbyte_server_port=8000,
        connection_id='5fdd8809-b7c6-4568-a6d2-72e17b648bb1',
        poll_interval_s=3,
        timeout=1800,
        status_updates=True
    )

    return airbyte_sync

@flow(name='dbt')
def run_dbt_commands():
    dbt_result = trigger_dbt_cli_command(command='dbt run',
        profiles_dir='C:\\Users\\JSchueller\\.dbt',
        project_dir='C:\\Users\\JSchueller\\etl-architecture-demo\\etl_architecture_demo',
        shell='powershell',
        return_all=True
    )
    
    return dbt_result

@flow(name='execute')
def execute():
    # run_airbyte_syncs()
    run_dbt_commands()

if __name__ == '__main__':
    execute()
terminal logs the following:
10:37:02.302 | INFO    | prefect.engine - Created flow run 'rose-zebu' for flow 'execute'
10:37:03.752 | INFO    | Flow run 'rose-zebu' - Created subflow run 'icy-wallaby' for flow 'dbt'
10:37:03.936 | INFO    | Flow run 'icy-wallaby' - Created task run 'trigger_dbt_cli_command-321ca940-0' for task 'trigger_dbt_cli_command'
10:37:03.937 | INFO    | Flow run 'icy-wallaby' - Executing 'trigger_dbt_cli_command-321ca940-0' immediately...
10:37:04.036 | INFO    | Task run 'trigger_dbt_cli_command-321ca940-0' - Running dbt command: dbt run --profiles-dir C:\Users\JSchueller\.dbt --project-dir C:\Users\JSchueller\etl-architecture-demo\etl_architecture_demo
10:37:09.409 | INFO    | Task run 'trigger_dbt_cli_command-321ca940-0' - Finished in state Completed()
10:37:09.508 | INFO    | Flow run 'icy-wallaby' - Finished in state Completed()
10:37:09.581 | INFO    | Flow run 'rose-zebu' - Finished in state Completed('All states completed.')
If I run the same dbt command in pwsh, dbt actually picks it up and runs it.
  ~   prefect-env 3.9.12  dbt run --profiles-dir 'C:\Users\JSchueller\.dbt' --project-dir 'C:\Users\JSchueller\etl-architecture-demo\etl_architecture_demo'
14:25:04  Running with dbt=1.2.1
14:25:04  Unable to do partial parsing because of a dbt version mismatch. Saved manifest version: 1.1.2. Current version: 1.2.1.
14:25:05  Found 3 models, 11 tests, 0 snapshots, 0 analyses, 267 macros, 0 operations, 0 seed files, 2 sources, 0 exposures, 0 metrics
14:25:05
14:25:09  Concurrency: 1 threads (target='dev')
14:25:09
14:25:09  1 of 3 START view model dbt.stg_accounts ....................................... [RUN]
14:25:11  1 of 3 OK created view model dbt.stg_accounts .................................. [SUCCESS 1 in 1.29s]
14:25:11  2 of 3 START view model dbt.stg_contacts ....................................... [RUN]
14:25:12  2 of 3 OK created view model dbt.stg_contacts .................................. [SUCCESS 1 in 1.27s]
14:25:12  3 of 3 START table model dbt.dim_accounts ...................................... [RUN]
14:25:23  3 of 3 OK created table model dbt.dim_accounts ................................. [SUCCESS 1 in 11.48s]
14:25:23
14:25:23  Finished running 2 view models, 1 table model in 0 hours 0 minutes and 17.79 seconds (17.79s).
14:25:23
14:25:23  Completed successfully
14:25:23
14:25:23  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3
versions: prefect: 2.4.2 (rebuilt my venv today, I was having this same problem running 2.3.2) prefect-dbt: 0.2.2
j

Jeff Hale

09/29/2022, 12:23 PM
Hi Joe. That’s strange. Are your running this locally? I wonder if the windows file path isn’t being parsed correctly. We’ve had a few issues with that elsewhere.
j

Joe Schueller

09/29/2022, 1:54 PM
I am running it locally, and I have a similar suspicion. I have some time to dig in further today so I'm going to play with it and report back.
:thank-you: 1
a

Andrew Huang

09/29/2022, 3:22 PM
are you able to run any commands using prefect_shell? if not, it could indicate there’s something wrong with the implementation for windows https://github.com/PrefectHQ/prefect-shell/blob/main/prefect_shell/commands.py#L63-L75
j

Joe Schueller

09/29/2022, 7:12 PM
I've just tried running this outside of a flow and received a warning back:
RuntimeWarning: coroutine 'shell_run_command' was never awaited
:
from prefect_shell.commands import *

c = "echo 'Hello world!'"

shell_run_command.fn(command=c, shell='powershell', return_all=True)
so I guess the event loop is closing before the command has a chance to run?
a

Andrew Huang

09/29/2022, 7:13 PM
shell_run_command.fn
without Prefect managing it is an async func; you may need to add
asyncio.run(shell_run_command.fn(...))
or wrap it in a Prefect flow
j

Joe Schueller

09/29/2022, 7:14 PM
ahhh okay
a

Andrew Huang

09/29/2022, 7:15 PM
thanks for testing!
j

Joe Schueller

09/29/2022, 7:17 PM
of course, I'm happy to have your help!
🙌 1
I have basically the same outcome as when running the dbt cli task. when the flow run completes, the temp file generated by
shell_run_command
is trying to be opened. I imagine that's not supposed to happen
a

Andrew Huang

09/29/2022, 7:22 PM
okay, so even
echo "hello world"
is not working?
import asyncio
from prefect_shell.commands import *

c = "echo 'Hello world!'"

asyncio.run(shell_run_command.fn(command=c, shell='powershell', return_all=True))
j

Joe Schueller

09/29/2022, 7:27 PM
correct
a

Andrew Huang

09/29/2022, 7:29 PM
I am not familiar with how powershell works, but does powershell allow you to execute a shell script? internally, prefect-shell writes teh command into a file and executes that temporary file
in short, it’s executing
powershell prefect-temporary.sh
another point of failure on windows could be:
from anyio import open_process
not sure how well supported that is https://github.com/PrefectHQ/prefect-shell/blob/main/prefect_shell/commands.py#L75 another thing to check is if you set the log levels to debug
stream_level=logging.DEBUG
unfortunately, I don’t have access to a windows machine to test these out myself
j

Joe Schueller

09/29/2022, 7:39 PM
when you say
prefect-temporary.sh
, do you mean specifically a bash script?
a

Andrew Huang

09/29/2022, 7:40 PM
er just a temporary file without any extension
with tempfile.NamedTemporaryFile(prefix="prefect-") as tmp:
        if helper_command:
            tmp.write(helper_command.encode())
            tmp.write(os.linesep.encode())
        tmp.write(command.encode())
        tmp.flush()
Windows also joins the command:
if sys.platform == "win32":
            shell_command = " ".join(shell_command)
j

Joe Schueller

09/29/2022, 7:46 PM
just to confirm, that's writing out what I provide in
shell_run_command
to
prefect-temporary
(whatever it winds up naming it), then executing as
[shell] prefect-temporary
, correct?
ok, yes, I think that's what's going on. powershell rejects the temp file because it has no extension, it seems. it thinks it's a command. doing the same in bash (running linux via WSL) runs it from that temp file just fine.
a

Andrew Huang

09/29/2022, 7:54 PM
thanks for helping debug. would you be comfortable submitting a PR to fix this? I imagine an
if shell.lower() == "powershell": suffix = ".ps1"
and pass suffix to `
NamedTemporaryFile(suffix=suffix)
j

Joe Schueller

09/29/2022, 7:56 PM
that makes sense
I can certainly try - this would be my first for an open source project! 😅
👍 1
🙌 1
a

Andrew Huang

09/29/2022, 7:57 PM
awesome to hear! feel free to ask me questions if you encounter any issues
👍 2
m

Michael Holvey

09/30/2022, 12:18 AM
Just picking up on this thread as I think I'm having similar issues running on Windows. Except I'm getting an error saying that I'm providing an invalid project directory (it's valid). @Joe Schueller - can you provide your final code if you were able to get it working? I also don't see
trigger_dbt_cli_command
taking a shell param in the docs?
j

Joe Schueller

09/30/2022, 3:38 PM
Hey @Michael Holvey! Still chopping away at this problem, but learning a whole lot about how Windows handles temporary files lol. I'm coming to the conclusion that this simply needs to be handled in a different fashion for Windows, but I also want to make sure I'm not upending the existing source code.
trigger_dbt_cli_command
is a part of the
prefect_dbt
library: https://prefecthq.github.io/prefect-dbt/
m

Michael Holvey

09/30/2022, 3:44 PM
Oh boy. I wonder if there are any workarounds to orchestrate dbt projects on Windows then? Wondering if I should try something like Airflow to get my client off the ground but I think it's just as much trouble to get it working on Windows 😞
j

Joe Schueller

09/30/2022, 6:37 PM
I've heard similar with respect to Airflow. This is more an issue of Windows being difficult with regards to running shell commands than it is Prefect or dbt. I have a solution, just need to code it out and hand it back for approval. I'm planning on having the PR submitted this weekend.
:thank-you: 1
👍 2
m

Michael Holvey

09/30/2022, 7:42 PM
Thanks so much, I look forward to it!!!
j

Joe Schueller

09/30/2022, 9:26 PM
I wound up with some unexpected free time this afternoon, so I got down to business. PR is submitted ☺️
👀 1
:thank-you: 1
a

Andrew Huang

09/30/2022, 9:28 PM
thanks so much! I’ll take a look
💯 1
Left some comments. Let me know if you need help with any of the comments!
j

Joe Schueller

10/02/2022, 7:05 PM
What you have makes sense, I didn't think to include some tests. I'm finding that all of the existing tests besides
test_run_shell_command_no_output
fail when using powershell because it handles things so differently. I'm trying to wrap my head around how to deal with this and I'm not getting far. any ideas spring to mind?
and not just powershell issues, I guess, some differences with windows as well. for instance, there is no system environment variable called $HOME on windows -- the equivalent would be USERPROFILE
a

Andrew Huang

10/03/2022, 7:53 PM
I think separate test for powershell would be ok. for the existing tests, I think you can use pytest.skipif windows. then have a test_commands_windows.py also pytest.skipif https://docs.pytest.org/en/7.1.x/how-to/skipping.html Let me know if that makes sense
j

Joe Schueller

10/06/2022, 9:22 PM
I think we're in pretty good shape now, but please let me know your feedback when you have an opportunity to review!
@Andrew Huang you rock! thank you for being patient with me in working through this. I've learned A LOT in the process!!
🙌 2
a

Andrew Huang

10/07/2022, 10:52 PM
Thank you for helping us get Windows supported! Will let you know once it’s been released. Hopefully by tomorrow!
:blob-attention-gif: 1
👀 1
m

Michael Holvey

10/07/2022, 10:55 PM
Thank you BOTH!
:party-parrot: 2
💯 2
a

Andrew Huang

10/07/2022, 11:50 PM
Okay prefect-shell v0.1.2 is now released on pypi (
pip install -U prefect-shell
)
😛anda-dancing: 1
:thank-you: 1
:party-parrot: 1