A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


EC2 and Redshift - Set Password File - use Bash / PSQL / Select without interactive password

1. Install PSQL on the machine you plan on connecting to Redshfit with. 

I used an Amazon Linux AMI

Once connected through SSH run the following: 

> sudo yum update
> sudo yum install postgresql

 

Test that you can connect to your Redshift Server. 

> psql -h my-connection-string -p my-port# -U user -d mydb


you'll be promted for the pasword. Once this works, we can setup the PGPASSFILE.

2. Create the Password File.

$ touch ~/.pgpass
$ chmod 0600 ~/.pgpass

Use your favorite text editor to add the following line to the file.

hostname:port:database:username:password


You can find more information regarding this file here : https://www.postgresql.org/docs/9.2/static/libpq-pgpass.html


3. Create a shell script to test your auto authentication. <yourscript>.sh

#!/bin/sh
set PGPASSFILE=~/.pgpass
psql -h <host url> -p <port> -U <user> -d <db_name>  -c "Select 'Hello World' "
echo Done

Save this script on your ec2 instance. Run it.

$ sh <yourscript>.<ext>

Predict stock prices with SQL Server Analysis Services Mining Models

Predictive analytics and supervised machine learning with SSAS and C#.

 

In this demo I use MS Time Series Mining structure to predict stock prices using the Auto Regressive Integrated Moving Average (ARIMA) method. 

  1. Data Mining and Machine Learning have been available in SQL Server since 2000.
  2. You don't need Cubes to use the mining structures.
  3. Accessing the models with C# and SSMS is easy

It's a basic mining structure example to demonstrate what SQL Server has been capable of. Please leave a comment if it helped or if you have any suggestions. 

The walk through consist of:

Creating a model:

Querying a model:

Querying a prediction from the model with a C# application: