SnowSQL/SnowSQL/public/Invoke-SnowSql.ps1 at a3731b52e4ab4ecb503fb81e2d8cb131e8f90410 · loandepot-oss/SnowSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
function Invoke-SnowSql
{
    <#
    .SYNOPSIS
    Invokes a Snowflake SQL statement

    .DESCRIPTION
    Invokes a Snowflake SQL statement

    .EXAMPLE
    Open-SnowSqlConnection
    Invoke-SnowSql -Query '!help'

    .NOTES
    Error handling does not exist at the moment
    #>

    [cmdletbinding(
        DefaultParameterSetName = 'QueryConnection',
        SupportsShouldProcess
    )]
    param(
        # Snowflake endpoint (ex: contoso.east-us-2.azure)
        [Parameter(ParameterSetName = 'QueryCred', Mandatory)]
        [Parameter(ParameterSetName = 'PathCred', Mandatory)]
        [string]
        $Endpoint,

        # Credential for snowflake endpoint
        [Parameter(ParameterSetName = 'QueryCred', Mandatory)]
        [Parameter(ParameterSetName = 'PathCred', Mandatory)]
        [PSCredential]
        $Credential,

        # An existing connection made with Open-SnowSqlConnection
        [Parameter(ParameterSetName = 'QueryConnection')]
        [Parameter(ParameterSetName = 'PathConnection')]
        [PSTypeName('SnowSql.Connection')]
        $Connection,

        # Query to invoke
        [Parameter(ParameterSetName = 'QueryCred')]
        [Parameter(ParameterSetName = 'QueryConnection')]
        [string[]]
        $Query = '!help',

        # SnowSql script file to execute
        [Parameter(ParameterSetName = 'PathCred')]
        [Parameter(ParameterSetName = 'PathConnection')]
        [string]
        $Path
    )

    begin
    {
        try
        {
            $snowSql = Get-Command snowsql -ErrorAction Stop |
                Select-Object -First 1 -ExpandProperty Source
        }
        catch
        {
            Write-Error "Could not find [snowsql.exe] on local system. Install snowsql.exe and make it available in the %path%. Install instructions can be found here [https://docs.snowflake.net/manuals/user-guide/snowsql-install-config.html]" -ErrorAction Stop
        }

        if($PSCmdlet.ParameterSetName -match 'Connection$')
        {
            if( -not $Connection )
            {
                $Connection = Get-SnowSqlConnection
            }
            $Credential = $Connection.Credential
            $Endpoint = $Connection.Endpoint
        }
    }

    process
    {
        $singleLineQuery = $Query -join [environment]::NewLine

        # make sure the query is not too long for the commandline
        $maxCommandLength = 500
        if ($singleLineQuery.Length -ge $maxCommandLength)
        {
            $Path = New-TemporaryFile
            Write-Verbose "Saving query to file [$Path]"
            $Query | Set-Content -Path $Path -Encoding UTF8
        }

        $snowSqlParam = @(
            '--accountname', $Endpoint
            '--username', $Credential.UserName
            '--option', 'exit_on_error=true'
            '--option', 'output_format=csv'
            '--option', 'friendly=false'
            '--option', 'timing=false'
            if ($Debug)
            {
                '--option', 'log_level=DEBUG'
            }
            if ($Path)
            {
                '--filename', $Path
            }
            else
            {
                '--query', $singleLineQuery
            }
        )

        Write-Debug ("Executing [& '$snowsql' $snowSqlParam]" -f $snowsql)
        if ($PSCmdlet.ShouldProcess("Execute SnowSql on [$Endpoint] as [$($Credential.UserName)]. Use -Debug to see full command"))
        {
            $env:SNOWSQL_PWD = $Credential.GetNetworkCredential().password
            $results = & $snowsql @snowSqlParam | ConvertFrom-Csv
            $env:SNOWSQL_PWD = ""
            Write-Verbose "LastExitCode[$LastExitCode]"
        }

        $results
    }
}