Tech and T-Accounts

SQL ETL Flip-Flop to deliver no data outage experience 

Thursday, July 19, 2018 9:31:24 AM

I know that we were all taught to delete then replace as the standard practice for ETL. I personally don’t prefer that approach for these reasons:

  1. When an ETL goes foul, the prior table’s data was truncated or the table dropped. Reverting is possible if a backup exists, but it is a painful process most often avoided.
  2. The drop/truncate then select/insert into approach typically takes many minutes to perform. During that time, queries will return incomplete information (either none at all, or just the rows that have been ETL’d so far when query ran).
  3. During the ETL, while the end table is in flux, end users are without good data.

The approach I prefer involves swapping a table from one schema to another (I refer to it as a flip-flop) – this swap process takes under a second, so the benefits are:

  1. The end user can always rely on the table data – it is always a complete table from the ETL.
  2. IT can easily revert to the prior table if the ETL results are found to be wanting for whatever reason. The end user effect is that they can continue using the older data while the ETL result is reviewed/repaired.

To set up the schema swap process, I establish two additional schema – new and old (but call them what you want). The new schema is where the table from the ETL is built and indexes added. The old schema is used to move the current dbo (or whatever schema it resides in) table so simple reversion can occur if needed.

Steps (entire flip-flop process takes under a second, no matter what the size of the table):

  1. Either create table or select into ETL target table in the 'new' schema. Create indexes against as needed. These steps are basically the same as what is typically done in an ETL process, only done against a target table that isn't the currently active data.
  2. Prepare for what I refer to as the 'flip-flop' process.
    a. Get rid of the table if it exists in the 'old' schema
         if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'old' and TABLE_NAME = 'ETL Target Table') Drop Table [old].[ETL Target Table];
    b. Place the current table into the 'old' schema
         Alter Schema old TRANSFER [dbo].[ETL Target Table];
    c. Move the 'new' ETL Target Table into the desired schema
         if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'new' and TABLE_NAME = 'ETL Target Table') Alter Schema dbo TRANSFER [new].[ETL Target Table];

Obfuscate username and passwords in Powershell 

Wednesday, December 20, 2017 3:20:40 PM

There are a whole host of blogs on how to encrypt passwords using PowerShell.  For the best security, those approaches should be followed, not this approach.  I wanted to not include my password and username information in scripts, yet also not go through the setup required for encryption to work (from what I gathered, this encryption is machine and user specific -- not the setup tasks that I wanted to pursue.)

The steps are similar -- create a file with the text coded, then read that file and decode the text to use in the PowerShell script.

PowerShell to code text to a file:

#this is simple obfuscation method for password not appearing as clear text in powershell script
 
# change the three items below to satisfy your needs
$acc = "myaccount"
$pass = "mypassword"
$filelocation = "E:\folder_where_files_are_saved"
 
[System.Text.Encoding]::Unicode.GetBytes($acc) | Set-Content $filelocation'\am.txt' 
[System.Text.Encoding]::Unicode.GetBytes($pass) | Set-Content $filelocation'\amp.txt' 
 
 
Then, in a PowerShell script to use the saved files, add these rows to retreive the username and password:
$filelocation = "E:\folder_where_files_are_saved"
 
# Get username and password from files created by obfuscation method
$gu = Get-Content $filelocation'\am.txt'  -ReadCount 0
$username = [system.text.encoding]::Unicode.GetString($gu)
 
$gp = Get-Content $filelocation'\amp.txt'  -ReadCount 0
$password = [system.text.encoding]::Unicode.GetString($gp)

Tableau Server refresh all extracts using REST API 

Tuesday, December 19, 2017 3:11:00 AM

Just worked through the 10.3 REST API for refreshing data extracts on the server.  The documentation language was all there, just not tremedously clear as the explanation paragraphs state that some parameters need to appear, yet the example doesn't reflect those parameters being present.  The missing piece -- adding the -ContentType parameter to the invoke statement is alluded to in the instructions, but as stated before, not present in the example.

I work with PowerShell to invoke the REST API as PowerShell comes with every Windows PC, which is what use.  The syntax shown below would need to be changed if some other shell language were used.

 

Powershell to Kickoff Tableau Server Extract Refresh - all extracts:

 

#Tableau Server - where data resides and extracts need refreshing

# Setup to connect

$server = "http://tableauserver"

$s = Invoke-RestMethod -Uri $server/api/2.4/serverinfo -Method get #works on server version 10.1 and later

$api = $s.tsResponse.serverInfo.restApiVersion  #10.3 server

 

$username = “some_tableau_admin_username”

$password = “password for above user”

$sitelogin = "mysite"  #site name where extracts exist

 

# generate body for sign in

$signin_body = (’<tsRequest>

  <credentials name=“’ + $username + ’” password=“’+ $password + ’” >

    <site contentUrl="'+$sitelogin +'" />

  </credentials>

</tsRequest>’)

$response = Invoke-RestMethod -Uri $server/api/$api/auth/signin -Body $signin_body -Method post

# save the auth token, site id and my user id

$authToken = $response.tsResponse.credentials.token

$siteID = $response.tsResponse.credentials.site.id

$myUserID = $response.tsResponse.credentials.user.id

$siteURL = $response.tsResponse.credentials.site.contentUrl

 

# set up header fields with auth token

$headers = New-Object “System.Collections.Generic.Dictionary[[String],[String]]”

 

# add X-Tableau-Auth header with our auth token

$headers.Add(“X-Tableau-Auth”, $authToken)

 

#tests whether logged in user is an Administrator  (site or server)

$loginUserid = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/users/$myUserID -Headers $headers -Method Get

$admin = $loginUserid.tsResponse.user.siteRole -like "*Administrator"

 

#create body element required for refresh extract POST

$refresh_body = (

’<tsRequest>

</tsRequest>’

)

 

# only admin can perform extractRefresh, so this next set of commands only execute when logged in user is an admin

if ($admin)

{

#get list of extract tasks from server -- Tableau 10.3 REST API can only kick off an extract task, not an individual Extract refresh

#this gets list of all extracts -- no filter applied to select a schedule, owner, priority, workbook, etc.

$extractlist = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/tasks/extractRefreshes -Headers $headers -Method Get

 

Foreach ($taskid in $extractlist.tsResponse.tasks.task.extractrefresh.id)

{

$runnow = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/tasks/extractRefreshes/$taskid/runNow -Headers $headers -Body $refresh_body -ContentType "application/xml" -Method POST

}

}

Page 1 of 6 1 2 3 4 5 6 > >> 
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2018 KB Earle Associates LLC | |