Retrieve Denodo Delegated Queries

Delegated_Queries.ps1

[cmdletbinding()]
param (
    [Parameter(Mandatory=$true)] [string] $WhereClause,
    [Parameter(Mandatory=$true)] [string] $ExportFilename,
    [switch] $ForceExport
)

# Load ODBC utility functions
. $PSScriptRoot\odbc_utils.ps1

# Constants
$STRING_MAX_SIZE = 65000
$EXCEL_CELL_LIMIT = 32767

$ExportToExcel = $true

$Query = @"
SELECT autoincrementid , cast(query as TEXT) as query, duration, numrows
  FROM denodo_logs.dbo.request_notification
 WHERE notificationtype = 'endRequest'
   AND requesttype  = 'SELECT VIEW'
   AND state = 'OK'
   --
   AND $WhereClause
"@

Write-Verbose "Query to retrieve log data:`n${Query}"

$MSSQLConnString = Get-MsSQLConnString `
                        -Encrypt no `
                        -Driver 'ODBC Driver 18 for SQL Server' `
                        -Server 'AcmeMSSQLServer' `
                        -Database 'denodo_logs' `
                        -ApplicationIntent ReadOnly
                       
Write-Host 'Retrieving logging data...'
# Retrieve the data from the logging table
$LoggingData = Run-OdbcSQL -ConnString $MSSQLConnString -Query $Query -Timeout 60
Write-Verbose "$($LoggingData.Rows.Count) record(s) retrieved"

# Add a column to store the delegated query
$LoggingData.Columns.Add('query_source', [string]) | Out-Null

# Template query
# CAST is necessary to prevent the error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
# Also, the size of the varchar needs to match the MaxLongVarcharSize parameter of the ODBC connection
$TemplateQuery = "SELECT cast(delegated_query as VARCHAR(${STRING_MAX_SIZE})) as delegated_query FROM GET_DELEGATED_SQLSENTENCE() WHERE vdp_query = '#DENODO_QUERY#'"

$DenodoConnString = Get-DenodoConnString `
                        -Server 'AcmeDenodoServer' `
                        -Credential (Get-Credential -UserName 'AcmeUser' -Message 'Denodo authentication') `
                        -MaxLongVarcharSize $STRING_MAX_SIZE

# Test connection before trying to get the delegated query for each Denodo query
try {
    Write-Verbose 'Testing connection to Denodo server...'
    Run-OdbcSQL `
        -ConnString $DenodoConnString `
        -Query 'DESC SESSION' | Out-Null
    Write-Verbose 'Connection OK'
} catch {
    Write-Warning "Unable to connect to server: $($_.Exception.Message)"
    break
}

# Retrieve the delegated query
$i=0
foreach ($Row in $LoggingData.Rows) {
    $i++
    Write-Progress -Activity "Retrieving delegated query" -PercentComplete (($i/$LoggingData.Rows.Count)*100)
    try {
        $DelQuery = Run-OdbcSQL `
                        -ConnString $DenodoConnString `
                        -Query $TemplateQuery.Replace('#DENODO_QUERY#', $Row.query.Replace("'", "''"))
        $SourceQuery = $DelQuery.Rows[0].delegated_query
        $Row.query_source = $SourceQuery

        if ($SourceQuery.Length -eq $STRING_MAX_SIZE) {
            Write-Warning "Source query might have been truncated for ID $($Row.autoincrementid) (max. of $STRING_MAX_SIZE characters)"
        }

        if ($SourceQuery.Length -gt $EXCEL_CELL_LIMIT) {
            Write-Warning "Source query length ($($SourceQuery.Length)) exceeds the cell character limit for Excel ($EXCEL_CELL_LIMIT) for ID $($Row.autoincrementid)."
            $ExportToExcel = $false
        }
    }
    catch {
        Write-Warning "Unable to get the delegated query for ID $($Row.autoincrementid): $($_.Exception.Message)"
    }
}
Write-Progress -Activity "Retrieving delegated query" -Completed

if ($ExportToExcel -or $ForceExport.IsPresent) {
    # If the file exists, delete it
    Remove-Item $ExportFilename -Force -ErrorAction SilentlyContinue

    # Make sure to truncate the queries to prevent a corrupted Excel file.
    $LoggingData.Rows | 
        Select-Object `
            autoincrementid, duration, numrows, `
            @{name='query'; expression = {$_.query.substring(0, [System.Math]::Min($_.query.length, $EXCEL_CELL_LIMIT)) } } `
            @{name='query_source'; expression = {$_.query_source.substring(0, [System.Math]::Min($_.query_source.length, $EXCEL_CELL_LIMIT)) } } |
        Export-Excel `
            -Path $ExportFilename `
            -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors
    Write-Host "Data exported to $ExportFilename"
} else {
    Write-Warning "Data cannot be saved to Excel due to length restrictions."
}

Write-Output $LoggingData

This script depends on odbc_utils.ps1.