Connecting to Mysql within Amazon VPC

Hi all,

It’s been a long time since my last post. Though days indeed :  lot of work and annoying health issues (cause and consequence, in that order). I’m back, with a lot of things to share about data processing / data warehousing / BI.

Let’s start with something small, but usefull : how to connect to an Amazon EC2 instance running mysql from within a VPC (Virtual Private Cloud) ? There is a small trick to know and to apply in order to open the connectivity.

First, you need a good understanding of the following :

• AWS : http://aws.amazon.com
• EC2 : http://aws.amazon.com/en/documentation/ec2/
• VPC : http://aws.amazon.com/en/documentation/vpc/
• MYSQL (and user mgmt) : http://dev.mysql.com/doc/refman/5.0/en/create-user.html

Back to good old practice : a drawing !

What we want to achieve is connecting and querying a MySQL database hosted on an EC2 instance. We want to connect and query from within the same VPC and subnet.

Have a closer look

This sounds easy, you would say : deploy mysql, create a database, create a user, grant this user for remote access, open security groups for the db port and go on … Well, according to the VPC and the subnet, this is not really that simple.

Let’s have a close look to our instances and have a quick review :

Using that setup – which is ok for any other cloud/on premises implementation – won’t be enough to give you access to your mysql database. Remember we are in a VPC and a subnet. Your connection request will be refused.

The solution

Could be hard to diagnose, but pretty easy to apply : configuring your MySQL user with read_user@% is not sufficient to grant access. You need to configure your user with providing the hostname of the mysql instance. Simple, mysql instance has internal ip = 10.0.0.4, means your hostname – Amazon style – is ip-10-0-0-4  (note : hyphens, not dots).

Then you simply have to configure your user as read_user@ip-10-0-0-4 then your connection will be granted !

Enjoy.

Amazon SDB bulk extractor

Hi all,

I said it, I did it. After coding a SDB bulk loader a few days ago, based on the batch insert feature from the AWS SDK, I coded a bulk extractor.

                                                         You said bulk, or Hulk ?

What’s the theory ?

sdb_extractor (its name) was coded with the following features in mind :

• multi query : you can ask multiple SQL orders (selects of course) to be executed and having the data extracted.
• multi domain : you can query multiple domains at the same time, using the same program execution,
• multi threaded : each query has its own thread !
• multi output format : each query generates data that goes into data files. These data files can have different delimiters, different names, different output formats (including json),
• query validation : each query is first tested and only the relevant ones (good syntax and existing data in the domain) will be executed (into its own thread as I said before).

How does it work ?

Simple as usual. A simple program needs a simple usage. Better than a long speech, let’s have a look at the –help command :

As you can see, nothing really complex. You have to provide the program with 2 parameters :

• --aws-credentials : a file containing your aws credentials. Usual syntax from aws (access key and private key),
• -- query-file : a file containing the queries you want to execute and grab the data coming from. Let’s have a close look to this file now.

 

The general process 

The control file : the query file.

This file is necessary in order to give instructions to sdb_extractor. Its syntax is very simple, here is the data format (without brackets) :

[SQL query order];[output file];[delimiter];[sdb domain]

… that gives in real life :

select `itemName()`, `Att1` from `your_domain` limit 2000;sdb_extract1.txt;semicolon;sdb.eu-west-1.amazonaws.com
select * from `your_domain_2`;sdb_extract2.txt;pipe;sdb.eu-west-1.amazonaws.com
select * from `your_domain_3`;sdb_extract3.txt;json;sdb.eu-west-1.amazonaws.com

The first query is asking the key (itemName()) and one attribute (Att1), is giving a limit of 2000 (chunks of 2000 lines will be streamed to the data file until no more rows remain), is asking for a txt file output having a semicolon delimiter. This query has to be run upon the EU West SDB domain.

Please note :

• The query file delimiter is : ;
• You can put as many queries as you want
• You can put as many lines as you want

The values for the delimiters are the following :

• semicolon,
• colon,
• pipe,
• json : in that case, a json syntax will be streamed to the export file.

Pretty simple, hum ?

Here is the program output after a full execution. As you can see, the execution is divided into 3 main parts :

• Query validation, showing OK or NOK (here only OK),
• Query running, with a counter. Note iterations with 2000 rows come from the “limit 2000” clause from the SQL query, while iterations with 100 rows are the default from SDB.
• Final output, showing OK for any successfully created file.

 

What happens when I write a bad SQL order ?

Very simple, the bad query will be trapped early and won’t generate any extraction thread. I managed to print some useful information from AWS. Here is an example of a failed query (I  intentionally remove a back tick from the domain name).

I want to try it !

No problem. Just download the package here. Includes the jar and a query file for example.

Performance

As usual, performance is a lot better when running the program from within Amazon EC2. In order to have and additional boost for your extraction, you could for instance create several SQL orders for the same domain/table but using different WHERE clause in order to “partition” your query. It works.

More to come.

I’m currently adding some data streaming to s3 features !! Sounds usefull for backup jobs …

As usual, please, give me feedback either positive or negative. I received a *lot* of feedback from the previous sdb_loader, thanks for that !! Please continue !

Amazon SDB bulk loader

Hi all,

I’ve spent a long time working with AWS and especially SDB. Loading mass data into SDB is not very frequent, but when needed it often comes to an end : there is no utility to do that and it’s a pain in the neck to use an ETL for that task (IMHO ETLs struggle to load key value stores and other NoSQL stores). Note : in the past, I developed a Kettle plugin to load sdb. You can find the post here.

Fortunately, AWS is providing a very powerful API. So one, two, three, Java … I decided to code my own SDB loader.

 

What is SDB ?

SDB is a NoSQL – non relational – database. You store data as key-value pairs and you can query data items via web services requests. For more informations, please go on AWS SDB page.

In order to understand SDB data model, we have to define 3 components :

• Domains : you store data in domains. Domains are similar to tables, from the relational world.
• Items : items are unique keys for attributes,
• Attributes and values : attributes are similar to columns. You add values to attributes.

Better than a long speech, below is a representation of a domain. CustomerID is the item and “First name” to “Telephone” are attributes. (example taken from Amazon). To make a simple analogy, a domain is like an Excel spreadsheet.

CustomerID	First name	Last name	Street address	City	State	Zip	Telephone
123	Bob	Smith	123 Main St	Springfield	MO	65801	222-333-4444
456	James	Johnson	456 Front St	Seattle	WA	98104	333-444-5555

 

SDB limitations

SDB has some limitations, the most obvious are :

• 10 Gb per domain,
• 100 domains,
• 256 attributes per item,
• 1024 attribute value length.

You can learn more about SDB limitations here.

 

What is sdb_loader ?

sdb_loader is built around the “BatchPutAttributesRequest” API method, allowing to insert “chunks” of 25 rows (no more). Usage is very simple. Running sdb_loader requires four files :

• sdb_loader itself,
• a data file containing the data you want to load
• a control file, containing the sdb column names (attributes) and other parameter I will describe later on.
• an AWS credentials file, containing your accessKey and secretKey .

These three files are working together like this :

sdb_loader.jar : the main program. Has to be started with java –jar sdb_loader. You have different parameters to set up, below is the –help output.

data file : any data file. No header row. Usual delimiters canl be used : “;”, “,”, “|” or “\t”. Delimiters detection is automatic. I will add more delimiters if needed.

control file : a simple txt file describing the SDB attributes and a flag specifying whether or not to replace the attribute/value pair or to add a new attribute/value pair. The default setting is false. Note : choosing false can have a significant impact on performance. The values are delimited with a “;”. You don’t have to specify any key (the itemName()) because the loader will automatically use the first column of the data file as the key. So, in the control file, you just have to describe the attributes.

Here is an example of the control file :

And here is an example of a completely silly data file. As you can see, no header and the key (Item_1) is present as first column.

Item_1;Attribute_1;Attribute_1;Attribute_1;Attribute_1;Attribute_1;Attribute_1;Attribute_1;Attribute_1;Attribute_1
Item_2;Attribute_2;Attribute_2;Attribute_2;Attribute_2;Attribute_2;Attribute_2;Attribute_2;Attribute_2;Attribute_2
Item_3;Attribute_3;Attribute_3;Attribute_3;Attribute_3;Attribute_3;Attribute_3;Attribute_3;Attribute_3;Attribute_3
Item_4;Attribute_4;Attribute_4;Attribute_4;Attribute_4;Attribute_4;Attribute_4;Attribute_4;Attribute_4;Attribute_4
Item_5;Attribute_5;Attribute_5;Attribute_5;Attribute_5;Attribute_5;Attribute_5;Attribute_5;Attribute_5;Attribute_5

The command line is also very simple. Here is the –help command :

How to use sdb_loader ?

java -jar sdb_loader.jar --aws-credentials=aws_credentials.txt --control-filename=data_mapping.txt --data-filename=data.txt --domain-name=mass_load_domain --sdb-endpoint=sdb.eu-west-1.amazonaws.com –domainRecreate

Parameters explanation : 

• --aws-credentials : the file having your AWS credentials,
• --control-filename : the file having the attributes you want to load and the boolean flag specifying whether or not to replace the attribute/value pair or to add a new attribute/value pair,
• --data-filename : the data file (without header),
• --domain-name : the domain you want to load (domain = table),
• --sdb-endpoint : the endpoint for your sdb instance (eu, us, asia …),
• -domainRecreate : if present, the domain your specified in [—domain-name] will be dropped and re-built, otherwise the domain won’t be altered,
• -help : the help output.

Here is the program output, while running :

At the end of processing, you should be able to see the whole story :

Performances

SDB query (load or select) performance has a lot to do with bandwidth : loading data from your workstation is a lot slower than loading from EC2. That’s why I recommend to use sdb_Loader from an EC2 instance. Loading from a workstation, using a broadband connection behind a corporate firewall, will give you a 20 to 40 rows/second.

Have a look at the graph below. This data was captured from an EC2 instance running Ubuntu when loading a 10 attributes domain, for +1 million rows. As you can see, we are starting slow then peak performance comes quick and seems to be around 200 rows/second and remain stable over time.

Note that I’ve already had better row/sec, like 270 to 300 row/sec. Depends on time (better in the morning, here in France).

Increase performance with domain sharding

After the interesting reading of  “How to efficiently forklift 1 billion rows into SimpleDB”, from Sid Anand’s blog, I found an interesting idea : domain sharding.

You can create several domains (ex : 100), and then pump all your data into sdb domains with several parallel executions of sdb_loader.

Hey, sounds interesting, I want to try it !

No problem, just download the jar here and have fun. You will also find a typical control file and an AWS credential file. The jar is working with java 1.7.

What’s next now ?

If you like or dislike sdb_loader, please let me know. I love having feedback and sharing with people. You can also follow me on Twitter (@vincentteyssier). Of course, feel free to give me any feedback, feature request and/or bug warning.

After the loader, now, I’m starting an extractor. Work is in progress and I have in mind to create a multi threaded extractor (ability to extract from more than a query/domain at a time).

On top of that, I’m currently having a close look to DynamoDB and I will start a loader + extractor in the next coming weeks.