Powershell - using ODBC and the Simba Driver to query AWS Athena

 I was running into issues with the Linked Server lopping off long JSON that I'm having to pull out from the raw files.  I can't explain it - doesn't appear to be SSMS.  See previous post

But I needed to automate this, rather than use SQL Workbench, save to "Excel" (it was XML), then opening it again and saving it so that instead of 250mb, it's 30mb.  Runs against the previous month, one day at a time (walking the partitions), and then saves to a file.  You got your Athena, your ODBC, your Export-Excel...

#This has two requirements:

# 1 - simba installed as an ODBC driver, called "your_simba_athena_dsn_name_here".  Free from Amazon for this use.

# 2 - ImportExcel.  Requires Powershell V4 (V5?), but for sure v3 won't work.

#Get-ODBC-Data2 is just a slight modification of Anders' code that includes the UID/PWD. Thanks!

#https://www.andersrodland.com/working-with-odbc-connections-in-powershell/

#modified because we have to pass in user/password to Athena.

function Get-ODBC-Data2{

    param(

    [string]$dsn,

    [string]$param,

    [string]$query=$(throw 'query is required.')

    )

    $conn = New-Object System.Data.Odbc.OdbcConnection

    $conn.ConnectionString = "DSN=$dsn;$param"

    $query

    $conn.open()

    $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)

    $ds = New-Object system.Data.DataSet

    (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null

    $conn.close()

    $ds.Tables[0]

 }

 #Set start and end.  Those can be whatever, but I made it one month for simplicity

 [datetime]$start_date_yyyymmdd = (get-date).addmonths(-1).tostring("yyyy-MM-01")

 [datetime]$end_date_yyyymmdd = $start_date_yyyymmdd.addmonths(1).tostring("yyyy-MM-01")

 #[datetime]$end_date_yyyymmdd = (get-date).addmonths(0).tostring("yyyy-MM-01")

 $filename = "Myfile_" + $start_date_yyyymmdd.tostring("yyyyMM")

 #set a variable so that as we walk through, we have an array to save everything to.

 $formattedresults=@()

 

 #loop through the days

 while ($start_date_yyyymmdd -lt $end_date_yyyymmdd) {

 $startdate = $start_date_yyyymmdd.tostring('yyyy-MM-dd')

 "starting $startdate"

 $query = @"

 select json_extract_scalar(myfield, '$.fieldjsonparsed') as fielda, dt,

 fieldb, fieldc

  from myschema.mytablename

  where dt='$startdate'

"@

 

 $results = get-odbc-data2 -query $query -dsn "your_simba_athena_dsn_name_here" -param "uid=youraccesskeyhere;PWD=yoursecretkeyhere" 

 #save non-systemdatarow version to our main array

 $formattedresults +=$results|select-object fielda,fieldb,fieldc 

  $start_date_yyyymmdd = $start_date_yyyymmdd.adddays(1)

 }

 

 #export using ImportExcel module, which unfortunately needs at least higher than V3

 $formattedresults| export-excel -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path c:\temp\$filename.xlsx

[AWS] Athena aka Hive/Presto - renaming/mapping a JSON attribute named TIMESTAMP to be called TS

(More Athena stuff coming, probably)

Since I couldn't get Amazon's documentation example with  ColumnToJsonKeyMappings to work... (fortunately, the Hive documentation is better, and Athena uses Hive for DDL)

My JSON looks like this (which I'm not formatting, because Athena demands every row be on one line, with no fancy "line feeds" or "carriage returns")

{"event":"eventififer","globalid":"3fd6dce3-6650-4e3f-8d8d-a46d1baaca02","timeStamp":"2020-06-08T19:29:31.114Z"}

In this case, "timestamp" is a reserved word, so you have to query it like:
and "timestamp" >= '2020-06-08T18:30:55Z' 

So, in order to rename the field, you could either create a view on top of it, or rename it during the creation.

CREATE EXTERNAL TABLE myawsdatacatalog.mytablename(
event string ,
globalid string,
ts string)
partitioned by (dt string)

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties ("mapping.ts"= "timestamp")
LOCATION 's3://mybucket/my/folder/path/';

Yes, the ts is a string, because it has to meet their particular format EXACTLY. If I try TIMESTAMP for the data type for ts, it creates fine! But when I try to query it, I get the following. 

HIVE_BAD_DATA: Error parsing field value '2020-06-04T20:01:47.829Z' for field 3: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

If you need a workaround, I'd say to create it as string, then toss a view on top with: 
select from_iso8601_timestamp(ts) as ts

[Powershell] loading a module if the SQL Server Agent can't do so automatically

I'm trying to use the SQL Server Agent to run a Powershell script that calls AWS.

But it failed.  

The term 'get-awscredentials' is not recognized as the   name of a cmdlet, function, script file, or operable program. Check the   spelling of the name, or if a path was included, verify that the path is   correct and try again.  

Which is odd because if I start a brand-new ISE on the box, it works.  Grr.

Okay, I must've forgotten the module.  But again, I don't need it with the ISE...

import-module awspowershell

But I got a different error:

import-module : The specified module 'awspowershell' was not loaded because no   valid module file was found in any module directory.  

Okay, so let's add it.

Where the heck is the module, anyways?

From the ISE, after loading the module by hand, run:

(Get-Module -ListAvailable awspowershell*).path

Then, now that we know that path:

import-module -name "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\awspowershell"

 

(note awspowershell is in there twice - it's looking for the... PSD1, I think?)

[AWS RDS] Sending an email when there's a new version of RDS available

Something I needed recently.  Difficulty level: Get-RDSPendingMaintenanceAction ONLY returns info when there's something to be done.  If you're current, there's no way to find out what it looks like.

This will email you a HTML-formatted table with the details of each pending maintenance action for every instance in a region.

[AWS] Reading Aurora audit logs in Cloudwatch for DDL changes

Another form of log-watching, this time the Audit logs that Aurora can push to Cloudwatch.  It's not all stuff by any means; heck, the example they use is just about failed logins.

In this case, we're reading the Cloudwatch logs for our RDS clusters.
Since Cloudwatch offers filters, in this example we're looking for QUERY commands, since we've turned Server Audit on, and are uploading connects + queries to Cloudwatch.  (More information on that in: https://aws.amazon.com/blogs/database/monitoring-amazon-aurora-audit-events-with-amazon-cloudwatch/).  Remember to set a cap on it, though then interesting things could be buried.

Why are we doing this?  In our case, this gives us a few bits of cool info.  Specifically, we can log all the DDL commands that come across our cluster, making sure everything is behaving as expected.

[AWS] Aurora - reading the errorlog files with powershell

Been working on monitoring.  For some reason, when you tell Aurora to send errorlogs to Cloudwatch, all it sends are the Audit Logs, which will tell you that code had changed, etc, but doesn't (!?!?!??!!!) actually put your logs in Cloudwatch.  I don't understand it, so I built this process to look through logs and return the data.  The next step would be to format it and either upload to Cloudwatch manually, or log it, or send email.  Whatever works for you.  You be you.  :D

[AWS] Querying CloudWatch logs using powershell to find DDL changes in your Aurora instance.

Did I get enough buzzwords in that post title? 

So, I've been working on an AWS Aurora MySQL project lately (yes, you need to qualify Aurora, because these days there's a Postgres variant).

One problem we had was getting info out of the logs.  Querying the logs is possible from within MySQL, but time consuming.  Ditto querying the logs that are being saved to disk.  We used the AWS directions (https://aws.amazon.com/blogs/database/monitoring-amazon-aurora-audit-events-with-amazon-cloudwatch/) to get our logs to dump into CloudWatch, which offers a Powershell cmdlet to filter them (although it limits to 1mb at a time, hence the DO WHILE with a low rowcount).  Unfortunately, I didn't find any actual working examples. 

So while this may not be the best, it does work. 

NOTE: results currently go to an out-gridview window.  Obviously, change that to what you need, or:
* remove the write-host
* remove the ogv line
* wrapper the entire DO WHILE into a variable then return that at the end. BE CAREFUL DOING THIS. I tried it with a decent-sized results set (30k) and got a weird error back:

Get-CWLFilteredLogEvent : Error unmarshalling response back from AWS. Request ID:

[AWS] Installing powershell, basics, and adding multiple tags to RDS instances with Powershell

The better-formatted parts are courtesy of coworker.  : )

In the AWS Console: Go to IAM, create a new user, set yourself up for programmatic access with an access key.  Download the credentials in the CSV.

Go download the Powershell Cmdlets for AWS.

AWS Powershell.  Download and install the AWSPowershell cmdlets

1. Download from https://aws.amazon.com/powershell/.  
   1. If you already have the AWS Powershell cmdlets installed and you need to install a later version, UNINSTALL the existing version before installing the new version. 
2. Run the msi to install the cmdlets.
3. Start PowerShell ISE as Administrator.  Determine which version of PowerShell is installed by running $PSVersionTable.PSVersion.
4. If you have version 3 or later, type "import-module awspowershell" at the ISE command prompt.  For earlier versions, use:  Import-Module -Name "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\AWSPowerShell.dll".  
   1. If you don't want to run this manually each time you load it, you can add it to your powershell profile, but it takes 20 seconds or so to load.  
   2. Alternatively, you can create a startup script to run each time you need to use the cmdlets.
   3. To verify the modules were loaded, run the command "Get-Module".  You should see the line "Binary     AWSPowerShell".  To determine the version of the tools, run the command "Get-AWSPowerShellVersion" or "Get-AWSPowerShellVersion -ListServiceVersionInfo", which includes the AWS services that are supported.
5. See http://docs.aws.amazon.com/powershell/latest/userguide/pstools-getting-set-up.html#pstools-config-ps-window for more information on how to get started with the AWS cmdlets.

For a session, you'll have to import the module.  You can do this by adding it to your profile (though it takes 20 seconds or so), or just run it by hand.

(powershell 3+)
import-module awspowershell
or
(powershell 2?) 
Import-Module -Name "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\AWSPowerShell.dll".  

• Set up your credentials and save them in a local (and encrypted, I believe) store on your desktop.  

set-awscredential  -AccessKey your_access_key -SecretKey your_secret_key -StoreAs default    



Adding tags to my clusters
(The biggest trick here was the "array of arrays" for the key/value.  Normal powershell splatting is name/value, which chokes when trying to insert into AWS' key/value convention.  Tried to find other ways to do it, but his works and is relatively easy.  If you have a better way, let me know! (as per https://aws.amazon.com/blogs/developer/tagging-amazon-ec2-instances-at-launch/).

Grab each matching instance, then give them those 4 tags

$Tags = @( @{key="tagname1";value="1"}, `
           @{key="tagname2";value="narf"}, `
           @{key="Description";value="yet more info"}, `
           @{key="Servertype";value="testAurora"} )

get-rdsdbinstance -Region us-west-1 | where {$_.DBClusterIdentifier -like '*aurora*'} | %{
add-rdstagstoresource -resourcename $_.DBInstanceArn -Tag $Tags -region us-west-1}

[AWS] Series of small posts coming. "Yeah, I didn't think I was going to AWS either"

New tag notification.  AWS.

Lessons learned, etc.  Decent bit of powershell in here too.