Recent changes to wiki

Oracle_To_Redshift_Data_Loader modified by Alex Buzunov

2016-04-07T12:47:15.411000Z

--- v1
+++ v2
@@ -11,7 +11,7 @@
  - It's executable (Oracle_To_Redshift_Loader.exe)  - no need for Python install.
  - It's 64 bit - it will work on any vanilla DOS for 64-bit Windows.
  - AWS Access Keys are not passed as arguments. 
- - Written using Python/boto/PyInstaller.
+ - Written using Python/boto/psycopg2/PyInstaller.

 ##Version
@@ -36,6 +36,7 @@
 - Data is loaded to Redshift using  COPY command
 - It's a Python/boto script
    * Boto S3 docs: http://boto.cloudhackers.com/en/latest/ref/s3.html
+   * psycopg2 docs: http://initd.org/psycopg/docs/ 
 - Executable is created using [pyInstaller] (http://www.pyinstaller.org/)

 ##Audience

Oracle_To_Redshift_Data_Loader modified by Alex Buzunov

2016-04-07T12:46:43.121000Z

Oracle-to-Redshift-Data-Loader

Let's you stream your Oracle table/query data to Amazon-Redshift from Windows CLI (command line).

Features:
- Streams Oracle table data to Amazon-Redshift.
- No need to create CSV extracts before load to Redshift.
- Data stream is compressed while load to Redshift.
- No need for Amazon AWS CLI.
- Works from your OS Windows desktop (command line).
- It's executable (Oracle_To_Redshift_Loader.exe) - no need for Python install.
- It's 64 bit - it will work on any vanilla DOS for 64-bit Windows.
- AWS Access Keys are not passed as arguments.
- Written using Python/boto/PyInstaller.

Version

OS	Platform	Version
Windows	64bit	[1.2 beta]

Purpose

Stream/pipe/load Oracle table data to Amazon-Redshift.

How it works

Tool connects to source Oracle DB and opens data pipe for reading.
Data is pumped to S3 using multipart upload.
Optional upload to Reduced Redundancy storage (not RR by default).
Optional "make it public" after upload (private by default)
If doesn't, bucket is created
You can control the region where new bucket is created
Streamed data can be tee'd (dumped on disk) during load.
If not set, S3 Key defaulted to query file name.
Data is loaded to Redshift using COPY command
It's a Python/boto script
- Boto S3 docs: http://boto.cloudhackers.com/en/latest/ref/s3.html
Executable is created using [pyInstaller] (http://www.pyinstaller.org/)

Audience

Database/ETL developers, Data Integrators, Data Engineers, Business Analysts, AWS Developers, DevOps,

Designated Environment

Pre-Prod (UAT/QA/DEV)

Usage

c:\Python35-32\PROJECTS\Ora2S3>dist\oracle_to_Redshift_loader.exe
#############################################################################
#Oracle to Redshift Data Loader (v1.2, beta, 04/05/2016 15:11:53) [64bit]
#Copyright (c): 2016 Alex Buzunov, All rights reserved.
#Agreement: Use this tool at your own risk. Author is not liable for any damages
#           or losses related to the use of this software.
################################################################################
Usage:
  set AWS_ACCESS_KEY_ID=<you access key>
  set AWS_SECRET_ACCESS_KEY=<you secret key>
  set ORACLE_LOGIN=tiger/scott@orcl
  set ORACLE_CLIENT_HOME=C:\app\oracle12\product\12.1.0\dbhome_1

  oracle_to_s3_loader.exe [<ora_query_file>] [<ora_col_delim>] [<ora_add_header>]
                            [<s3_bucket_name>] [<s3_key_name>] [<s3_use_rr>] [<s3_public>]

        --ora_query_file -- SQL query to execure in source Oracle db.
        --ora_col_delim  -- CSV column delimiter (|).
        --ora_add_header -- Add header line to CSV file (False).
        --ora_lame_duck  -- Limit rows for trial load (1000).
        --create_data_dump -- Use it if you want to persist streamed data on your filesystem.

        --s3_bucket_name -- S3 bucket name (always set it).
        --s3_location    -- New bucket location name (us-west-2)
                                Set it if you are creating new bucket
        --s3_key_name    -- CSV file name (to store query results on S3).
                if <s3_key_name> is not specified, the oracle query filename (ora_query_file) will be used.
        --s3_use_rr -- Use reduced redundancy storage (False).
        --s3_write_chunk_size -- Chunk size for multipart upoad to S3 (10<<21, ~20MB).
        --s3_public -- Make loaded file public (False).
        --redshift_table -- Target Redshift table.

        Oracle data uploaded to S3 is always compressed (gzip).

Example

Environment variables

Set the following environment variables (for all tests):
set_env.bat:

set AWS_ACCESS_KEY_ID=<you access key>
set AWS_SECRET_ACCESS_KEY=<you secret key>

set ORACLE_LOGIN=tiger/scott@orcl
set ORACLE_CLIENT_HOME=C:\\app\\oracle12\\product\\12.1.0\\dbhome_1

Test load with data dump.

In this example complete table test2 get's uploaded to Aamzon-S3 as compressed CSV file.

Contents of the file table_query.sql:

SELECT * FROM test2;

Also temporary dump file is created for analysis (by default there are no files created)
Use -s, --create_data_dump to dump streamed data.

If target bucket does not exists it will be created in user controlled region.
Use argument -t, --s3_location to set target region name

Contents of the file test.bat:

dist\oracle_to_s3_loader.exe ^
    -q table_query.sql ^
    -d "|" ^
    -e ^
    -b test_bucket ^
    -k oracle_table_export ^
    -r ^
    -p ^
    -s
    -t target_table

Executing test.bat:

c:\Python35-32\PROJECTS\Ora2Redshift>dist\oracle_to_redshift_loader.exe   -q table_query.sql      -d "|"  -e      -b test_bucket       -k oracle_table_export  -r      -p      -s
Uploading results of "table_query.sql" to existing bucket "test_bucket"
Dumping data to: c:\Python35-32\PROJECTS\Ora2S3\data_dump\table_query\test_bucket\oracle_table_export.20160405_235310.gz
1 chunk 10.0 GB [8.95 sec]
2 chunk 5.94 GB [5.37 sec]
Uncompressed data size: 15.94 GB
Compressed data size: 63.39 MB
Load complete (17.58 sec).

Test query

Download

git clone https://github.com/alexbuz/Oracle_to_Redshift_Data_Loader
Master Release -- oracle_to_redshift_loader 1.2

FAQ

Can it load Oracle data to Amazon S3 file?

Yes, it is the main purpose of this tool.

Can developers integrate `Oracle_To_S3_Data_Uploader` into their ETL pipelines?

Yes. Assuming they are doing it on OS Windows.

How fast is data load using `Oracle_To_Redshift_Data_Loader`?

As fast as any implementation of multi-part load using Python and boto.

How to inscease load speed?

Input data stream is getting compressed before upload to S3. So not much could be done here.
You may want to run it closer to source or target for better performance.

What are the other ways to move large amounts of data from Oracle to S3?

You can write a sqoop script that can be scheduled as an 'EMR Activity' under Data Pipeline.

Does it create temporary data file to facilitate data load to S3?

Can I log transfered data for analysis?

Yes, Use -s, --create_data_dump to dump streamed data.

Explain first step of data transfer?

The query file you provided is used to select data form target Oracle server.
Stream is compressed before load to S3.

Explain second step of data transfer?

Compressed data is getting uploaded to S3 using multipart upload protocol.

Explain third step of data load. How data is loaded to Amazon Redshift?

You Redshift cluster has to be open to the world (accessible via port 5439 from internet).
It uses PostgreSQL COPY command to load file located on S3 into Redshift table.

What technology was used to create this tool

I used SQLPlus, Python, Boto to write it.
Boto is used to upload file to S3.
SQLPlus is used to spool data to compressor pipe.
psycopg2 is used to establish ODBC connection with Redshift clusted and execute COPY command.

Does it delete file from S3 after upload?

Does it create target Redshift table?

Where are the sources?

Please, contact me for sources.

Can you modify functionality and add features?

Yes, please, ask me for new features.

What other AWS tools you've created?

[Oracle_To_S3_Data_Uploader] (https://github.com/alexbuz/Oracle_To_S3_Data_Uploader) - Stream Oracle data to Amazon- S3.
[CSV_Loader_For_Redshift] (https://github.com/alexbuz/CSV_Loader_For_Redshift/blob/master/README.md) - Append CSV data to Amazon-Redshift from Windows.
[S3_Sanity_Check] (https://github.com/alexbuz/S3_Sanity_Check/blob/master/README.md) - let's you ping Amazon-S3 bucket to see if it's publicly readable.
EC2_Metrics_Plotter - plots any CloudWatch EC2 instance metric stats.
S3_File_Uploader - uploads file from Windows to S3.

Do you have any AWS Certifications?

Yes, AWS Certified Developer (Associate)

Can you create similar/custom data tool for our business?

Yes, you can PM me here or email at alex_buz@yahoo.com.
I'll get back to you within hours.

Oracle_To_S3_Data_Uploader modified by Alex Buzunov

2016-04-07T12:31:04.650000Z

Oracle-to-S3 data uploader.

Let's you stream your Oracle table/query data to Amazon-S3 from Windows CLI (command line).

Features:
- Streams Oracle table data to Amazon-S3.
- No need to create CSV extracts before upload to S3.
- Data stream is compressed while upload to S3.
- No need for Amazon AWS CLI.
- Works from your OS Windows desktop (command line).
- It's executable (Oracle_To_S3_Uploader.exe) - no need for Python install.
- It's 64 bit - it will work on any vanilla DOS for 64-bit Windows.
- AWS Access Keys are not passed as arguments.
- Written using Python/boto/PyInstaller.

Version

OS	Platform	Version
Windows	64bit	[1.2 beta]

Purpose

Stream (upload) Oracle table data to Amazon-S3.

How it works

Tool connects to source Oracle DB and opens data pipe for reading.
Data is pumped to S3 using multipart upload.
Optional upload to Reduced Redundancy storage (not RR by default).
Optional "make it public" after upload (private by default)
If doesn't, bucket is created
You can control the region where new bucket is created
Streamed data can be tee'd (dumped on disk) during upload.
If not set, S3 Key defaulted to query file name.
It's a Python/boto script
- Boto S3 docs: http://boto.cloudhackers.com/en/latest/ref/s3.html
Executable is created using [pyInstaller] (http://www.pyinstaller.org/)

Audience

Database/ETL developers, Data Integrators, Data Engineers, Business Analysts, AWS Developers, DevOps,

Designated Environment

Pre-Prod (UAT/QA/DEV)

Usage

c:\Python35-32\PROJECTS\Ora2S3>dist\oracle_to_s3_uploader.exe
#############################################################################
#Oracle to S3 Data Uploader (v1.2, beta, 04/05/2016 15:11:53) [64bit]
#Copyright (c): 2016 Alex Buzunov, All rights reserved.
#Agreement: Use this tool at your own risk. Author is not liable for any damages
#           or losses related to the use of this software.
################################################################################
Usage:
  set AWS_ACCESS_KEY_ID=<you access key>
  set AWS_SECRET_ACCESS_KEY=<you secret key>
  set ORACLE_LOGIN=tiger/scott@orcl
  set ORACLE_CLIENT_HOME=C:\app\oracle12\product\12.1.0\dbhome_1

  oracle_to_s3_uploader.exe [<ora_query_file>] [<ora_col_delim>] [<ora_add_header>]
                            [<s3_bucket_name>] [<s3_key_name>] [<s3_use_rr>] [<s3_public>]

        --ora_query_file -- SQL query to execure in source Oracle db.
        --ora_col_delim  -- CSV column delimiter (|).
        --ora_add_header -- Add header line to CSV file (False).
        --ora_lame_duck  -- Limit rows for trial upload (1000).
        --create_data_dump -- Use it if you want to persist streamed data on your filesystem.

        --s3_bucket_name -- S3 bucket name (always set it).
        --s3_location    -- New bucket location name (us-west-2)
                                Set it if you are creating new bucket
        --s3_key_name    -- CSV file name (to store query results on S3).
                if <s3_key_name> is not specified, the oracle query filename (ora_query_file) will be used.
        --s3_use_rr -- Use reduced redundancy storage (False).
        --s3_write_chunk_size -- Chunk size for multipart upoad to S3 (10<<21, ~20MB).
        --s3_public -- Make uploaded file public (False).

        Oracle data uploaded to S3 is always compressed (gzip).

Example

Environment variables

Set the following environment variables (for all tests):
set_env.bat:

set AWS_ACCESS_KEY_ID=<you access key>
set AWS_SECRET_ACCESS_KEY=<you secret key>

set ORACLE_LOGIN=tiger/scott@orcl
set ORACLE_CLIENT_HOME=C:\\app\\oracle12\\product\\12.1.0\\dbhome_1

Test upload with data dump.

In this example complete table test2 get's uploaded to Aamzon-S3 as compressed CSV file.

Contents of the file table_query.sql:

SELECT * FROM test2;

Also temporary dump file is created for analysis (by default there are no files created)
Use -s, --create_data_dump to dump streamed data.

If target bucket does not exists it will be created in user controlled region.
Use argument -t, --s3_location to set target region name

Contents of the file test.bat:

dist\oracle_to_s3_uploader.exe ^
    -q table_query.sql ^
    -d "|" ^
    -e ^
    -b test_bucket ^
    -k oracle_table_export ^
    -r ^
    -p ^
    -s

Executing test.bat:

c:\Python35-32\PROJECTS\Ora2S3>dist\oracle_to_s3_uploader.exe   -q table_query.sql      -d "|"  -e      -b test_bucket       -k oracle_table_export  -r      -p      -s
Uploading results of "table_query.sql" to existing bucket "test_bucket"
Dumping data to: c:\Python35-32\PROJECTS\Ora2S3\data_dump\table_query\test_bucket\oracle_table_export.20160405_235310.gz
1 chunk 10.0 GB [8.95 sec]
2 chunk 5.94 GB [5.37 sec]
Uncompressed data size: 15.94 GB
Compressed data size: 63.39 MB
Upload complete (17.58 sec).
Your PUBLIC upload is at: https://s3-us-west-2.amazonaws.com/test_bucket/oracle_table_export.gz

Download

git clone https://github.com/alexbuz/Oracle_To_S3_Data_Uploader
Master Release -- oracle_to_s3_uploader 1.2

FAQ

Can it load Oracle data to Amazon S3 file?

Yes, it is the main purpose of this tool.

Can developers integrate `Oracle_To_S3_Data_Uploader` into their ETL pipelines?

Yes. Assuming they are doing it on OS Windows.

How fast is data upload using `CSV Loader for Redshift`?

As fast as any implementation of multi-part load using Python and boto.

How to inscease upload speed?

Input data stream is getting compressed before upload to S3. So not much could be done here.
You may want to run it closer to source or target for better performance.

What are the other ways to move large amounts of data from Oracle to S3?

You can write a sqoop script that can be scheduled as an 'EMR Activity' under Data Pipeline.

Does it create temporary data file to facilitate data load to S3?

Can I log transfered data for analysis?

Yes, Use -s, --create_data_dump to dump streamed data.

Explain first step of data transfer?

The query file you provided is used to select data form target Oracle server.
Stream is compressed before load to S3.

Explain second step of data transfer?

Compressed data is getting uploaded to S3 using multipart upload protocol.

What technology was used to create this tool

I used SQLPlus, Python, Boto to write it.
Boto is used to upload file to S3.
SQLPlus is used to spool data to compressor pipe.

Where are the sources?

Please, contact me for sources.

Can you modify functionality and add features?

Yes, please, ask me for new features.

What other AWS tools you've created?

[CSV_Loader_For_Redshift] (https://github.com/alexbuz/CSV_Loader_For_Redshift/blob/master/README.md) - Append CSV data to Amazon-Redshift from Windows.
[S3_Sanity_Check] (https://github.com/alexbuz/S3_Sanity_Check/blob/master/README.md) - let's you ping Amazon-S3 bucket to see if it's publicly readable.
EC2_Metrics_Plotter - plots any CloudWatch EC2 instance metric stats.
S3_File_Uploader - uploads file from Windows to S3.

Do you have any AWS Certifications?

Yes, AWS Certified Developer (Associate)

Can you create similar/custom data tool for our business?

Yes, you can PM me here or email at alex_buz@yahoo.com.
I'll get back to you within hours.

EC2_Metrics_Plotter modified by Alex Buzunov

2016-04-07T12:29:06.268000Z

CloudWatch AWS/EC2 instance metrics plotter.

Purpose:
- Generate and plot statistics for CloudWatch EC2 instance.
- All Metrics and statistics are supported

Included:

Statistics:
Sum,Maximum,Minimum,SampleCount,Average
Metrics:
CPUUtilization,NetworkIn,NetworkOut,NetworkPacketsIn,
NetworkPacketsOut,DiskWriteBytes,DiskReadBytes,DiskWriteOps,
DiskReadOps,CPUCreditBalance,CPUCreditUsage,StatusCheckFailed,
StatusCheckFailed_Instance,StatusCheckFailed_System

Wrote using Python/boto3.
Compiled using PyInstaller

Version

OS	Platform	Version
Windows	32bit	[0.1.0 beta]

Purpose

Generate plots for AWS-ECS metrics and statistics.
Helps you generate plots on demand and review them using generated html report.

How it works

ec2metrics.exe connects to EC2 and reads datapoints for given CloudWatch EC2 instance/metric/statistic combo.
Using matplotlib plot is created and saved on the filesystem.
Html report is generated allowing preview saved metric plots.
It will not work for group CloudWatch EC2 instances metrics.

Audience

Database/ETL developers, Data Integrators, Data Engineers, Business Analysts, AWS Developers, DevOps

Designated Environment

Pre-Prod (UAT/QA/DEV)

Usage

C:\Python35-32>dist\ec2metrics\ec2metrics.exe
## Plots EC2 CPUUtilization metric for given instance id.
##
## Generates matplotlib plots for given instance/statistic/metric.
##
Usage:
  set AWS_ACCESS_KEY_ID=<you access key>
  set AWS_SECRET_ACCESS_KEY=<you secret key>
  set AWS_DEFAULT_REGION=<your region > (for example:us-west-2 )
  ec2metrics.exe [<instance>] [<period_min>] [<from_min>] [<to_min>]
        [<statistic>] [<metric_name>] [<namespace>]
        [<show_plot> or <show_report>]
        [<plot_dir>] [<plot_dir>]

        [-b] --instance         -- EC2 instance name (i-********).
        [-p] --period_min       -- Aggregation interval (5 min).
        [-f] --from_min         -- Start from, min (60).
        [-t] --to_min           -- End at, min (0 - present).
        [-s] --statistic        -- Statistic type (Average).
           Could be one of: Sum,Maximum,Minimum,SampleCount,Average
        [-m] --metric_name  -- Metric name (CPUUtilization)
           Could be one of:
                CPUUtilization,NetworkIn,NetworkOut,NetworkPacketsIn,
                NetworkPacketsOut,DiskWriteBytes,DiskReadBytes,DiskWriteOps,
                DiskReadOps,CPUCreditBalance,CPUCreditUsage,StatusCheckFailed,
                StatusCheckFailed_Instance,StatusCheckFailed_System
        [-g] --namespace        -- CloudWatch namespace,
        container for metric (AWS/EC2).

        [-r] --show_plot        -- Open plotter window (False).
        [-n] --show_report  -- Open browser with html report (True).

        [-d] --plot_dir         -- Target plot dir (plots).
        [-e] --plot_dir         -- Timestamp for to append to plot_dir
        (current date).

        Index.html is generated in <plot_dir>\<timestamp>

Environment variables

  set AWS_ACCESS_KEY_ID=<you access key>
  set AWS_SECRET_ACCESS_KEY=<you secret key>
  set AWS_DEFAULT_REGION=<your region > (for example:us-west-2 )

Examples

Plot "Average,Minimum" for "NetworkIn" CloudWatch EC2 metric.

ec2metrics.exe --instance i-fe9cea26 -f 1000  -p 10  -s Average,Minimum -m NetworkIn  -r

200(0100/0100): i-fe9cea26: NetworkIn: Sum
200(0100/0100): i-fe9cea26: NetworkIn: Maximum
200(0100/0100): i-fe9cea26: NetworkIn: Minimum
200(0100/0100): i-fe9cea26: NetworkIn: SampleCount
200(0100/0100): i-fe9cea26: NetworkIn: Average

Report is at: C:\Python35-32\plots\20160327_220118\index.html

Result:

Html report

Report is generated with preview for all plots created with this job.

Plot "Sum,Maximum,Minimum,SampleCount,Average" stats for "CPUUtilization" CloudWatch EC2 metric.

ec2metrics.exe --instance i-fe9cea26 -f 500  -p 1  -s Sum,Maximum,Minimum,SampleCount,Average -m CPUUtilization  -r
200(0084/0099): i-fe9cea26: CPUUtilization: Sum
200(0084/0099): i-fe9cea26: CPUUtilization: Maximum
200(0000/0099): i-fe9cea26: CPUUtilization: Minimum
200(0099/0099): i-fe9cea26: CPUUtilization: SampleCount
200(0084/0099): i-fe9cea26: CPUUtilization: Average

Report is at: c:\Python35-32\plots\20160328_113906\index.html

Result:

Plot "Sum,Maximum,Minimum,SampleCount,Average" stats for "CPUCreditUsage" CloudWatch EC2 metric.

ec2metrics.exe --instance i-fe9cea26 -f 6000  -p 30  -s Sum,Maximum,Minimum,SampleCount,Average -m CPUCreditUsage  -r -t 3000 -e CPUCreditUsage
200(0006/0027): i-fe9cea26: CPUCreditUsage: Sum
200(0006/0027): i-fe9cea26: CPUCreditUsage: Maximum
200(0001/0027): i-fe9cea26: CPUCreditUsage: Minimum
200(0027/0027): i-fe9cea26: CPUCreditUsage: SampleCount
200(0006/0027): i-fe9cea26: CPUCreditUsage: Average
Report is at: c:\Python35-32\plots\CPUCreditUsage\index.html

Result:

Plot all stats for all CloudWatch EC2 metrics.

c:\Python35-32>dist\ec2metrics\ec2metrics.exe --from_min 3000 --instance 'i-fe9cea26,i-fe9cea26' --metric_name CPUUtilization,NetworkIn,NetworkOut,NetworkPacketsIn,NetworkPacketsOut,DiskWriteBytes,DiskReadBytes,DiskWriteOps,DiskReadOps,CPUCreditBalance,CPUCreditUsage,StatusCheckFailed,StatusCheckFailed_Instance,StatusCheckFailed_System --namespace AWS/EC2 --period_min 1 --plot_dir C:\Python35-32\plots --statistic Average,Minimum,Maximum,Sum  --to_min 2000 -r -e All_Metrics

200(0174/0200): i-fe9cea26: CPUUtilization: Average
200(0001/0200): i-fe9cea26: CPUUtilization: Minimum
200(0174/0200): i-fe9cea26: CPUUtilization: Maximum
200(0174/0200): i-fe9cea26: CPUUtilization: Sum
200(0200/0200): i-fe9cea26: NetworkIn: Average
...
200(0000/1000): i-fe9cea26: StatusCheckFailed_Instance: Sum
200(0000/1000): i-fe9cea26: StatusCheckFailed_System: Average
200(0000/1000): i-fe9cea26: StatusCheckFailed_System: Minimum
200(0000/1000): i-fe9cea26: StatusCheckFailed_System: Maximum
200(0000/1000): i-fe9cea26: StatusCheckFailed_System: Sum

Report is at: C:\Python35-32\plots\All_Metrics\index.html

Result:

One of the plots:

Html report

Report is generated with preview for all plots created with this job.

Download

Master Release -- ec2metrics 0.1.0

S3_File_Uploader modified by Alex Buzunov

2016-04-07T12:27:36.602000Z

S3 File Uploader for Windows CLI.

Basic file to Amazon-S3 uploader.

Features:
- No need for Amazon AWS CLI
- Works from your OS Windows desktop (command line)
- Logs upload % progress to CLI screen.
- It's executable (s3_percent_upload.exe) - no need for Python install
- It's 32 bit - it will work on any vanilla Windows.
- Access keys are fed from CLI environment (not command line args)
- Written using Python/boto/PyInstaller

Version

OS	Platform	Version
Windows	64bit	[0.1.0 beta]

Purpose

Ad-hoc file upload to Amazon S3.
Optional upload to Reduced Redundancy storage (not RR by default).
Optional "make it public" after upload (private by default)
Custom S3 Key (defaulted to transfer file name)

Audience

Business Analysts, AWS Developers, DevOps,

Designated Environment

Pre-Prod (UAT/QA/DEV)

Amazon S3 data upload price

It's free to upload file to Amazon S3.
Storage will cost you.

Usage

## Upload file to S3.
##
## Upload % progress outputs to the screen.
##
Usage:
  set AWS_ACCESS_KEY_ID=<you access key>
  set AWS_SECRET_ACCESS_KEY=<you secret key>
  s3_percent_upload.exe <file_to_transfer> <bucket_name> [<s3_key_name>] [<use_rr>] [<public>]
        if <s3_key_name> is not specified, the filename will be used.
        --use_rr -- Use reduced redundancy storage.
        --public -- Make uploaded files public.

        Boto S3 docs: http://boto.cloudhackers.com/en/latest/ref/s3.html

Environment variables

Set the following environment variables:

set AWS_ACCESS_KEY_ID=<you access key>
set AWS_SECRET_ACCESS_KEY=<you secret key>

Example

Upload file to Amazon-S3 Reduced Redundancy storage and make in Publicly accessible

S3_RR_Public_upload.bat

set AWS_ACCESS_KEY_ID=<you access key>
set AWS_SECRET_ACCESS_KEY=<you secret key>

cd c:\tmp\S3_Uploader
s3_percent_upload.exe c:\tmp\data.zip test123 --use_rr -public

resutl.log (S3_RR_Public_upload.bat > resutl.log)

Connecting to S3...
File size: 388.5KiB
Public = True
ReducedRedundancy = True
Uploaded 0 bytes of 397799 (0%)
Uploaded 24576 bytes of 397799 (6%)
Uploaded 49152 bytes of 397799 (12%)
Uploaded 73728 bytes of 397799 (18%)
Uploaded 98304 bytes of 397799 (24%)
Uploaded 122880 bytes of 397799 (30%)
Uploaded 147456 bytes of 397799 (37%)
Uploaded 172032 bytes of 397799 (43%)
Uploaded 196608 bytes of 397799 (49%)
Uploaded 221184 bytes of 397799 (55%)
Uploaded 245760 bytes of 397799 (61%)
Uploaded 270336 bytes of 397799 (67%)
Uploaded 294912 bytes of 397799 (74%)
Uploaded 319488 bytes of 397799 (80%)
Uploaded 344064 bytes of 397799 (86%)
Uploaded 368640 bytes of 397799 (92%)
Uploaded 393216 bytes of 397799 (98%)
Upload complete.
Your file is at: https://s3-website-us-west-2.amazonaws.com/test123/data.zip

Time elapsed: 2.54299998283 seconds

Download

Master Release -- s3_percent_uploader 0.1.0

S3_Sanity_Check modified by Alex Buzunov

2016-04-07T12:26:09.666000Z

S3_Sanity_Check modified by Alex Buzunov

2016-04-07T12:25:34.359000Z

S3 bucket sanity check for Windows CLI.

Simple sanity (public access) check for Amazon-S3 bucket.

Features:
- Checks if given Amazon-S3 bucket is publicly accessible or not.
- No need for Amazon AWS CLI
- Works from your OS Windows desktop (command line)
- It's executable (s3sanity.exe) - no need for Python install
- It's 32 bit - it will work on any vanilla Windows.
- No AWS Access Keys needed.
- Written using Python/boto/PyInstaller

Version

OS	Platform	Version
Windows	32bit	[0.1.0 beta]

Purpose

s3sanity helps you confirm that given bucket is not publicly accessible.
Helps you find readable bucket on Amazon-S3 (for fun).

How it works

s3sanity.exe tries to read given bucket from Amazon-S3.
Prints success message if bucket is publicly accessible (readable).
Prints error message if bucket does not exists or not readable.
It will not elaborate why read try faied.

Audience

Database/ETL developers, Data Integrators, Data Engineers, Business Analysts, AWS Developers, DevOps

Designated Environment

Pre-Prod (UAT/QA/DEV)

Usage

C:\Python35-32>dist\s3sanity.exe
## S3 sanity check.
##
## Outputs access status to the screen.
##
Usage:

  s3_sanity.exe -b <bucket_name>

    -b [--bucket] -- S3 bucket name.

"""

Environment variables

No environment variables required because it's a public access test.

Examples

How to check if S3 bucket is publicly readable or not?

Testing if bucket `test` is readable by everyone.

C:\Python35-32>dist\s3sanity.exe -b test
#####################################
NO access OR does NOT exists ("test")
#####################################

Bucket test does not exist or unreadable by everyone.

Testing if bucket `test2` is readable by everyone.

C:\Python35-32>dist\s3sanity.exe -b test2

You HAVE access to "test2"

Horay! Bucket test2 is readable by everyone!

Testing if bucket "elvis" is readable by everyone.

C:\Python35-32>dist\s3sanity.exe -b elvis
#####################################
NO access OR does NOT exists ("elvis")
#####################################

Too bad. Bucket elvis does not exist or unreadable by everyone.

Testing if bucket "refuse" is readable by everyone.

C:\Python35-32>dist\s3sanity.exe -b refuse

You HAVE access to "refuse"

Horay! Bucket refuse is readable by everyone!

Download

Master Release -- s3sanity 0.1.0

FAQ

Can it tell if S3 bucket is unreadable?

No, it cannot really tell if it's unreadable or simply does not exists.

Do I need a password to use it?

No, no passwords or access keys.
We are testing public read access to a bucket.

How does it work?

I use Python boto (AWS API for Python) module to interact with AWS.
Boto checks header of the bucket. If successful - it's readable.

s3.meta.client.head_bucket(Bucket=bucket.name)

What are the other ways to do it?

You can use AWS CLI aws s3api to do the same.

Can I check multiple buckets for readability?

No. To do you have to call s3sanity.exe for each S3 bucket.

Does check contents of the bucket for readability?

Does it create any manifest files?

Can I use Linux.

No, only OS Windows for now.

What technology was used to create this tool

I used Python and Boto (AWS API for Python) to write it.

Where are the sources?

Please, contact me for sources.

Can you modify functionality and add features?

Yes, please, ask me for new features.

What other AWS tools you've created?

[CSV_Loader_For_Redshift] (https://github.com/alexbuz/CSV_Loader_For_Redshift/blob/master/README.md) - Append CSV data to Amazon-Redshift from Windows.
EC2_Metrics_Plotter - plots any CloudWatch EC2 instance metric stats.
S3_File_Uploader - uploads file from Windows to S3.

Do you have any AWS Certifications?

Yes, AWS Certified Developer (Associate)

Can you create similar/custom data tool for our business?

Yes, you can PM me here or email at alex_buz@yahoo.com.
I'll get back to you within hours.

CSV_Loader_For_Redshift modified by Alex Buzunov

2016-04-07T11:47:42.095000Z

CSV_Loader_For_Redshift modified by Alex Buzunov

2016-04-07T11:46:34.900000Z

CSV File Loader for Amazon Redshift DB.

Loads CSV file to Amazon-Redshift table from Windows command line.

Features:
- Loads local (to your Windows desktop) CSV file to Amazon Redshift.
- No need to preload your data to S3 prior to insert to Redshift.
- No need for Amazon AWS CLI.
- Works from your OS Windows desktop (command line).
- It's executable (csv_loader_for_redshift.exe) - no need for Python install.
- It's 32 bit - it will work on any vanilla Windows.
- AWS Access Keys are not passed as arguments.
- Written using Python/boto/PyInstaller.

Version

OS	Platform	Version
Windows	32bit	[0.1.0 beta]

Purpose

Ad-hoc CSV file load to Amazon Redshift table.

How it works

File is staged on S3 prior to load to Redshift
Optional upload to Reduced Redundancy storage (not RR by default).
Optional "make it public" after upload (private by default)
S3 Key defaulted to transfer file name.
Load is done using COPY command
Target Redshift table has to exist
It's a Python/boto/psycopg2 script
- Boto S3 docs: http://boto.cloudhackers.com/en/latest/ref/s3.html
- psycopg2 docs: http://initd.org/psycopg/docs/
Executable is created using [pyInstaller] (http://www.pyinstaller.org/)

Audience

Database/ETL developers, Data Integrators, Data Engineers, Business Analysts, AWS Developers, DevOps,

Designated Environment

Pre-Prod (UAT/QA/DEV)

Usage

## Load CSV file to Amazon Redshift table.
##
## Load % progress outputs to the screen.
##
Usage:  
  set AWS_ACCESS_KEY_ID=<you access key>
  set AWS_SECRET_ACCESS_KEY=<you secret key>
  set REDSHIFT_CONNECT_STRING="dbname='***' port='5439' user='***' password='***' host='mycluster.***.redshift.amazonaws.com'"  
  csv_loader_for_redshift.py <file_to_transfer> <bucket_name> [<use_rr>] [<public>]
                         [<delim>] [<quote>] [<to_table>] [<gzip_source_file>]

    --use_rr -- Use reduced redundancy storage (False).
    --public -- Make uploaded files public (False).
    --delim  -- CSV file delimiter (',').
    --quote  -- CSV quote ('"').
    --to_table  -- Target Amazon-Redshit table name.
    --gzip_source_file  -- gzip input CVS file before upload to Amazon-S3 (False).

    Input filename will be used for S3 key name.

    Boto S3 docs: http://boto.cloudhackers.com/en/latest/ref/s3.html
    psycopg2 docs: http://initd.org/psycopg/docs/

"""

Example

Environment variables

Set the following environment variables (for all tests:

set AWS_ACCESS_KEY_ID=<you access key>
set AWS_SECRET_ACCESS_KEY=<you secret key>

set REDSHIFT_CONNECT_STRING="dbname='***' port='5439' user='***' password='***' host='mycluster.***.redshift.amazonaws.com'"

CSV file upload into Redshift table `test2`

examples\Load_CSV_To_Redshift_Table.bat

set AWS_ACCESS_KEY_ID=<you access key>
set AWS_SECRET_ACCESS_KEY=<you secret key>
set REDSHIFT_CONNECT_STRING="dbname='***' port='5439' user='***' password='***' host='mycluster.***.redshift.amazonaws.com'"  

cd c:\tmp\CSV_Loader
csv_loader_for_redshift.exe c:\tmp\data.csv test123 -r -d "," -t test2 -z

resutl.log (Load_CSV_To_Redshift_Table.bat > resutl.log)

S3        | data.csv.gz | 100%
Redshift  | test2       | DONE
Time elapsed: 5.7 seconds

Test prerequisits.

Target Redshift table DDL

CREATE TABLE test2 (id integer , num integer, data varchar,num2 integer, data2 varchar,num3 
integer, data3 varchar,num4 integer, data4 varchar);

Test data

Test data is in file examples\data.csv

Sources

Will add as soon as I clean em up and remove all the passwords and AWS keys :-)).

Download

git clone https://github.com/alexbuz/CSV_Loader_For_Redshift
Master Release -- csv_loader_for_redshift 0.1.0

FAQ

Can it load CSV file from Windows desktop to Amazon Redshift.

Yes, it is the main purpose of this tool.

Can developers integrate CSV loader into their ETL pipelines?

Yes. Assuming they are doing it on OS Windows.

How fast is data upload using `CSV Loader for Redshift`?

As fast as any AWS API provided by Amazon.

How to inscease upload speed?

Compress input file or provide -z or --gzip_source_file arg in command line and this tool will compress it for you before upload to S3.

What are the other ways to upload file to Redshift?

You can use 'aws s3api' and psql COPY command to do pretty much the same.

Can I just zip it using Windows File Explorer?

No, Redshift will not recognize *.zip file format.
You have to gzip it. You can use 7-Zip to do that.

Does it delete file from S3 after upload?

Does it create target Redshift table?

Is there an option to compress input CSV file before upload?

Yes. Use -z or --gzip_source_file argument so the tool does compression for you.

Explain first step of data load?

The CSV you provided is getting preloaded to Amazon-S3.
It doesn't have to be made public for load to Redshift.
It can be compressed or uncompressed.
Your input file is getting compressed (optional) and uploaded to S3 using credentials you set in shell.

Explain second step of data load. How data is loaded to Amazon Redshift?

You Redshift cluster has to be open to the world (accessible via port 5439 from internet).
It uses PostgreSQL COPY command to load file located on S3 into Redshift table.

Can I use WinZip or 7-zip

Yes, but you have to use 'gzip' compression type.

What technology was used to create this tool

I used Python, Boto, and psycopg2 to write it.
Boto is used to upload file to S3.
psycopg2 is used to establish ODBC connection with Redshift clusted and execute COPY command.

Where are the sources?

Please, contact me for sources.

Can you modify functionality and add features?

Yes, please, ask me for new features.

What other AWS tools you've created?

[S3_Sanity_Check] (https://github.com/alexbuz/S3_Sanity_Check/blob/master/README.md) - let's you ping Amazon-S3 bucket to see if it's publicly readable.
EC2_Metrics_Plotter - plots any CloudWatch EC2 instance metric stats.
S3_File_Uploader - uploads file from Windows to S3.

Do you have any AWS Certifications?

Yes, AWS Certified Developer (Associate)

Can you create similar/custom data tool for our business?

Yes, you can PM me here or email at alex_buz@yahoo.com.
I'll get back to you within hours.

Discussion for Home page

2016-04-07T11:44:47.520000Z

CSV_Loader_For_Redshift.zip

Loads CSV file to Amazon-Redshift table from Windows command line.

Features:

Loads local (to your Windows desktop) CSV file to Amazon Redshift.
No need to preload your data to S3 prior to insert to Redshift.
No need for Amazon AWS CLI.
Works from your OS Windows desktop (command line).
It's executable (csv_loader_for_redshift.exe) - no need for Python install.
It's 32 bit - it will work on any vanilla Windows.
AWS Access Keys are not passed as arguments.
Written using Python/boto/PyInstaller.

Recent changes to wiki

Oracle_To_Redshift_Data_Loader modified by Alex Buzunov

Oracle_To_Redshift_Data_Loader modified by Alex Buzunov

Oracle-to-Redshift-Data-Loader

Version

Purpose

How it works

Audience

Designated Environment

Usage

Example

Environment variables

Test load with data dump.

Test query

Download

FAQ

Can it load Oracle data to Amazon S3 file?

Can developers integrate Oracle_To_S3_Data_Uploader into their ETL pipelines?

How fast is data load using Oracle_To_Redshift_Data_Loader?

How to inscease load speed?

What are the other ways to move large amounts of data from Oracle to S3?

Does it create temporary data file to facilitate data load to S3?

Can I log transfered data for analysis?

Explain first step of data transfer?

Explain second step of data transfer?

Explain third step of data load. How data is loaded to Amazon Redshift?

What technology was used to create this tool

Does it delete file from S3 after upload?

Does it create target Redshift table?

Where are the sources?

Can you modify functionality and add features?

What other AWS tools you've created?

Do you have any AWS Certifications?

Can you create similar/custom data tool for our business?

Links

Oracle_To_S3_Data_Uploader modified by Alex Buzunov

Oracle-to-S3 data uploader.

Version

Purpose

How it works

Audience

Designated Environment

Usage

Example

Environment variables

Test upload with data dump.

Download

FAQ

Can it load Oracle data to Amazon S3 file?

Can developers integrate Oracle_To_S3_Data_Uploader into their ETL pipelines?

How fast is data upload using CSV Loader for Redshift?

How to inscease upload speed?

What are the other ways to move large amounts of data from Oracle to S3?

Does it create temporary data file to facilitate data load to S3?

Can I log transfered data for analysis?

Explain first step of data transfer?

Explain second step of data transfer?

What technology was used to create this tool

Where are the sources?

Can you modify functionality and add features?

What other AWS tools you've created?

Do you have any AWS Certifications?

Can you create similar/custom data tool for our business?

Links

EC2_Metrics_Plotter modified by Alex Buzunov

CloudWatch AWS/EC2 instance metrics plotter.

Version

Purpose

How it works

Audience

Designated Environment

Usage

Environment variables

Examples

Plot "Average,Minimum" for "NetworkIn" CloudWatch EC2 metric.

Result:

Html report

Plot "Sum,Maximum,Minimum,SampleCount,Average" stats for "CPUUtilization" CloudWatch EC2 metric.

Result:

Plot "Sum,Maximum,Minimum,SampleCount,Average" stats for "CPUCreditUsage" CloudWatch EC2 metric.

Can developers integrate `Oracle_To_S3_Data_Uploader` into their ETL pipelines?

How fast is data load using `Oracle_To_Redshift_Data_Loader`?

Can developers integrate `Oracle_To_S3_Data_Uploader` into their ETL pipelines?

How fast is data upload using `CSV Loader for Redshift`?

Testing if bucket `test` is readable by everyone.

Testing if bucket `test2` is readable by everyone.