Hi everyone. I'm hoping someone can enlighten me o...
# ask-community
j
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
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
sure thing
🙏 1
Here's how my .py looks right now. (the airbyte portion is working just fine!)
Copy code
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:
Copy code
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.
Copy code
  ~   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
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
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.
🙏 1
a
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
I've just tried running this outside of a flow and received a warning back:
RuntimeWarning: coroutine 'shell_run_command' was never awaited
:
Copy code
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
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
ahhh okay
a
thanks for testing!
j
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
okay, so even
echo "hello world"
is not working?
Copy code
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
correct
a
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
when you say
prefect-temporary.sh
, do you mean specifically a bash script?
a
er just a temporary file without any extension
Copy code
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:
Copy code
if sys.platform == "win32":
            shell_command = " ".join(shell_command)
j
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
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 `
Copy code
NamedTemporaryFile(suffix=suffix)
j
that makes sense
I can certainly try - this would be my first for an open source project! 😅
👍 1
🙌 1
a
awesome to hear! feel free to ask me questions if you encounter any issues
👍 2
m
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
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
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
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.
🙏 1
👍 2
m
Thanks so much, I look forward to it!!!
j
I wound up with some unexpected free time this afternoon, so I got down to business. PR is submitted ☺️
👀 1
🙏 1
a
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
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
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
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
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
Thank you BOTH!
🦜 2
💯 2
a
Okay prefect-shell v0.1.2 is now released on pypi (
pip install -U prefect-shell
)
panda dancing 1
🙏 1
🦜 1