Tyler Simpson
05/18/2023, 11:52 PMdbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target silver --models s_t_jobs_growth --full-refresh
dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target gold --models g_v_jobs_daily_total g_v_jobs_daily_weekly_monthly_growth --full-refresh
I have tried a few approaches with and without Prefect blocks.
I first attempted a dbt Core Operation block but I was unsure of how to format the paths/directories. The same format as the bash dbt run above did not work but this gave different errors:
C:\Users\simps\p3dd_phase_3_due_diligence\project\dbt\p3dd
I also tried running with prefect shell to avoid dbt build-ins but I received powershell specific errors and couldn't seem to get it to run via bash.
I am hoping someone can help provide a basic skeleton code to help me run my commands above or provide insight on altering my approaches.Sean Williams
05/19/2023, 5:24 PMfrom prefect import flow
from prefect_dbt.cli.commands import DbtCoreOperation
@flow
def trigger_dbt_flow() -> str:
result = DbtCoreOperation(
commands=["pwd", "dbt debug", "dbt run"],
project_dir="PROJECT-DIRECTORY-PLACEHOLDER",
profiles_dir="PROFILES-DIRECTORY-PLACEHOLDER"
).run()
return result
trigger_dbt_flow()
\p3dd
to one of your examples, but not others. I wonder if that might be part of the problemTyler Simpson
05/19/2023, 5:48 PMSean Williams
05/19/2023, 5:55 PMprofiles_dir
and adding a --profiles-dir
flag in the dbt command, I think you're specifying the directory twice. Tough to say without more info, but I could see that causing the problem. If you change the dbt command to dbt debug
I'd be curious to see if that changes anythingTyler Simpson
05/19/2023, 5:57 PMfrom prefect import flow
from prefect_dbt import DbtCoreOperation
@flow
def trigger_dbt_flow() -> str:
result = DbtCoreOperation(
commands=["dbt debug"],
project_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt\\p3dd",
profiles_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt"
).run()
return result
if __name__ == '__main__':
trigger_dbt_flow()
Oddly it seems I am getting a powershell access/security error. I do not use powershell generally I only execute bash locally and in my venv. I am using C:\Users\simps where this appears to be routing to my C:\Users\Tyler Simpson\. It seems that perhaps I need to either force bash to be used or alter the pathing of the powershell. What do you think?
$ python test.py
153247.565 | INFO | prefect.engine - Created flow run 'cornflower-raven' for flow 'trigger-dbt-flow'
153247.785 | INFO | Flow run 'cornflower-raven' - PID 25344 triggered with 1 commands running inside the '.' directory.
153248.691 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output:
. : File C:\Users\Tyler
Simpson\OneDrive\Documents\WindowsPowerShell\profile.ps1 cannot be loaded.
153248.693 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output:
The file C:\Users\Tyler
153248.694 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output:
Simpson\OneDrive\Documents\WindowsPowerShell\profile.ps1 is not digitally
signed. You cannot run this script on the current system. For more
information about running scripts and setting execution policy, see
about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:3
+ . 'C:\Users\Tyler Simpson\OneDrive\Documents\WindowsPowerShell\profil ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess
153248.722 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output:
C:\Users\TYLERS~1\AppData\Local\Temp\prefect-si1q5fye.ps1 : File
C:\Users\Tyler Simpson\AppData\Local\Temp\prefect-si1q5fye.ps1 cannot be
loaded. The file C:\Users\Tyler
Simpson\AppData\Local\Temp\prefect-si1q5fye.ps1 is not digitally signed.
153248.724 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output:
You cannot run this script on the current system. For more information
about running scripts and setting execution policy, see
about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170.
153248.726 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output:
At line:1 char:1
+ C:\Users\TYLERS~1\AppData\Local\Temp\prefect-si1q5fye.ps1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess
153248.747 | ERROR | Flow run 'cornflower-raven' - Encountered exception during execution:
Traceback (most recent call last):
File "C:\Users\simps\p3dd_phase_3_due_diligence\project\prefect_pipelines\p3ddenv\lib\site-packages\prefect\engine.py", line 669, in orchestrate_flow_runfrom prefect import flow
from prefect_dbt import DbtCoreOperation
@flow
def trigger_dbt_flow() -> str:
result = DbtCoreOperation(
commands=["dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target silver --models s_t_jobs_growth --full-refresh"],
project_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt\\p3dd",
profiles_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt"
).run()
return result
if __name__ == '__main__':
trigger_dbt_flow()
Powershell security was getting in the way which was resolved quickly and easily.
First, opening powershell as an admin.
Get-ExecutionPolicy
If this results in Restricted or AllSigned that is preventing the script execution. In my case it was set to AllSigned
Set-ExecutionPolicy Unrestricted
This then allowed the script to run.
Thank you for your assistance!Sean Williams
05/19/2023, 7:54 PMTyler Simpson
05/19/2023, 8:47 PM