Skip to content

Instantly share code, notes, and snippets.

@kspeeckaert
Created April 19, 2022 15:10
Show Gist options
  • Save kspeeckaert/0056598bdbaa60359520aabb50acac4a to your computer and use it in GitHub Desktop.
Save kspeeckaert/0056598bdbaa60359520aabb50acac4a to your computer and use it in GitHub Desktop.
Retrieve Denodo Delegated Queries
[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
@kspeeckaert
Copy link
Author

This script depends on odbc_utils.ps1.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment