Shantanu's Blog

Database Consultant

June 04, 2016

elasticsearch docker image with scripting support

The default elasticsearch image does not support scripting. So I have created a new image that anyone can download from...

Here are the steps used to create the new image.

# cat elasticsearch.yml
script.inline: true
script.indexed: true
network.host: 0.0.0.0

# cat Dockerfile
from elasticsearch
copy elasticsearch.yml /usr/share/elasticsearch/config/elasticsearch.yml

# docker build -t shantanuo/elasticsearch-script .

# docker push shantanuo/elasticsearch-script

I can now run the container based on the image I just created.

# docker run -d -p 9200:9200 -p 9300:9300 shantanuo/elasticsearch-script

Find the name of the container and link it to kibana like this...

# docker run -d -p 5603:5601 --link stoic_goldstine:elasticsearch -e ELASTICSEARCH_URL=http://elasticsearch:9200 kibana

# or use this to start es with kibana
docker run -d -p 9200:9200 -p 9300:9300 -p5603:5601 -e ES_HEAP_SIZE=1g --name myelastic shantanuo/elastic

_____

This image is based on official elasticsearch image. If I need to build everything from scratch, then I can use Ubuntu official image as shown here...

https://hub.docker.com/r/shantanuo/elasticsearch/

Labels: docker, elastic, linux tips, noSQL, shell script, splunk, usability

posted by shantanu # Saturday, June 04, 2016 0 comments

June 03, 2016

Save your packets to elasticsearch

Here are 2 docker commands to start elasticsearch with kibana.

docker run -d -p 9200:9200 -p 9300:9300 --name elasticsearch-pb elasticsearch
docker run -d -p 5601:5601 --name kibana-pb --link elasticsearch-pb:elasticsearch -e ELASTICSEARCH_URL=http://elasticsearch:9200 kibana

You can now push logs to elasticsearch using logstash or use beat.
Install beat package on the server from where you want to push the logs...

deb:

sudo apt-get install libpcap0.8
curl -L -O https://download.elastic.co/beats/packetbeat/packetbeat_1.2.3_amd64.deb
sudo dpkg -i packetbeat_1.2.3_amd64.deb

rpm:

sudo yum install libpcap
curl -L -O https://download.elastic.co/beats/packetbeat/packetbeat-1.2.3-x86_64.rpm
sudo rpm -vi packetbeat-1.2.3-x86_64.rpm

### Config yml file

vi /etc/packetbeat/packetbeat.yml

# Multiple outputs may be used.
output:
### Elasticsearch as output
elasticsearch:
# hosts: ["localhost:9200"]
# hosts: ["ec2-54-65-142-180.compute-1.amazonaws.com"]
hosts: ["search-es-demo-jyhk2or3v3sesrgt6dgn5u7qm.us-east-1.es.amazonaws.com:443"]
protocol: "https"

# A template is used to set the mapping in Elasticsearch
template:
# Path to template file
path: "packetbeat.template.json"

### start packetbeat

/etc/init.d/packetbeat start
_____

The packetbeat configuration file without comments looks something like this...

# cat /etc/packetbeat/packetbeat.yml | grep -v '#' | grep -v '^$'
interfaces:
device: any
protocols:
dns:
ports: [53]
include_authorities: true
include_additionals: true
http:
ports: [80, 8080, 8000, 5000, 8002]
memcache:
ports: [11211]
mysql:
ports: [3306]
pgsql:
ports: [5432]
redis:
ports: [6379]
thrift:
ports: [9090]
mongodb:
ports: [27017]
output:
elasticsearch:
hosts: ["search-es-demo-jyt2or3v3sesrgt6dgn5u7qm.us-east-1.es.amazonaws.com:443"]
protocol: "https"
template:
path: "packetbeat.template.json"
shipper:
logging:
files:

Labels: docker, elastic, noSQL, splunk, usability

posted by shantanu # Friday, June 03, 2016 0 comments

May 23, 2016

load data into elastic using logstash

# install logstash

wget https://download.elastic.co/logstash/logstash/packages/centos/logstash-2.3.2-1.noarch.rpm

rpm -iUh logstash-2.3.2-1.noarch.rpm

# test logstash can generate json output

bin/logstash -e 'input {file {path => "/var/log/cron" start_position => beginning} } output {stdout { codec => json } } '

# push data to elastic
cd /opt/logstash/
bin/logstash -e 'input {file {path => "/var/log/cron" start_position => beginning} } output { elasticsearch { hosts => "https://search-xxxx-demo-75hhndin3fhmovqanbqwfimu4q.us-east-1.es.amazonaws.com"} } '

Labels: aws, elastic, noSQL, shell script, usability

posted by shantanu # Monday, May 23, 2016 0 comments

August 14, 2015

mongoDB mysql comparison cheat sheet

db.users.find({}, {})	select * from users
db.users.find({}, {“username”:1, “email”:1}}	select username, email from users
db.users.find({}, {“username”:1, “email”:1}}.limit(10)	select username, email from users limit 10
.limit() .skip() .sort()
db.users.count()	select count(*) from users
db.runCommand(“distinct”: “users”, “key”: “age”})	select distinct(age) from users
db.users.find({“age”:{“$gte”:18, “$lte”:30}})	select * from users where age >= 18 and age <= 30
$lt $gt $lte $get $ne $elemMatch
db.users.find({“ticket_no”:{“$in”:[75, 390]}})	select * from users where ticket_no in (“75”, “390”)
$in $nin $not $all
db.users.find({“$or”:[{“ticket_no”:{“$in”:[75, 390]}}, {“winner”:true}]})	select * from users where ticket_no in (“75”, “390”) or winner is not null
$or $and $nor $elemMatch
db.users.find({“age”:{“$in”:[null], “$exists”:true}})	select * from users where age is null
db.users.find({“username”:/happy?/i})	select * from users where username like ‘happy%’
perl compatible regular expressions
db.users.find({“ticket_no”:75})	select * from users where ticket_no like ‘%75%’
[75, 390, 120, 450]	“75”, “390”, “120”, “450”
db.users.find({“ticket_no.2”:120})
db.users.find({“ticket_no”:{“$size”:4}})
db.users.findOne({criteria as above}, {“$slice”:[23, 10]}})	select * from users where age >= 18 and age <= 30 limit 23, 10
db.runCommand($getLastError”:1})	show warnings;
db.articles.aggregate("$project": {"author":1}}, {$group":{"_id":"$author", "count":{"$sum":1}}}, {"$sort": {"count": -1}}, {"$limit":5}	Select author, count(*) as cnt from articles group by author order by cnt desc limit 5
Aggregation results are limited to maximum response time of 16 MB
db.employees.aggregate( {"$project": {"totalPay" : {"$subtract" : [{"$add": ["$salary", "$bonus"]}, "$taxes"] } } } )	Select (salary + bouns – taxes) as totalPay from employees
$add $subtract $multiply $divide $mod
db.employees.aggregate( { "$project" : { "tenure" : {"$subtract" : [{"$year" : new Date()}, {"$year": "$hireDate"}] } } } )	select year(now()) – year(hireDate) as tenure from employees
$year $month $week $dayOfMonth $dayOfWeek $dayOfYear $hour $minute $second
db.employees.aggreage( { "$project": { "email" : { "$concat" : [ {"$substr" : [ "$firstName", 0, 1]}, ".", "$lastName", "@company.com" ] } } } )	select concat(left(firstName, 1), “.”, lastName, “@company.com”) as email from employees
$substr $concat $toLower $toUpper
db.sales.aggregate( { "$group": { "_id": "$country", "totalRevenue": { "$sum" : "$revenue" } } } )	select country, sum(revenue) from sales group by country
db.blog.aggregate({"$project": {"comments": "$comments"}}, {"$unwind" : "$comments"}, {"$match": {"comments.author" : "Akbar" }})

Labels: mongodb, mysql, noSQL, usability

posted by shantanu # Friday, August 14, 2015 0 comments

January 24, 2015

query text files

Usually we need to import the CSV data to SQL in order to run the queries. How about running queries directly on the text file?

# git clone https://github.com/harelba/q.git

# cd q

# chmod +x ./bin/q

# ./bin/q "SELECT COUNT(1) FROM examples/exampledatafile"
248

If git is not installed, use the single file found here...

# wget -O q https://cdn.rawgit.com/harelba/q/1.5.0/bin/q?source=install_page&table=1

Interesting!

Labels: mysql tips, noSQL, python

posted by shantanu # Saturday, January 24, 2015 0 comments

July 04, 2012

Cassandra

http://cassandra.apache.org

Entity–attribute–value model (EAV) also known as vertical database model is a data model to describe entities where the number of attributes are not known or are frequently changing. This is fundamentally different than SQL model and better than key-value store like Redis. Installation Cassandra is straight forward and commands are similar to redis set / get.

create keyspace abc;

use abc;

create column family users with comparator=AsciiType and column_metadata=[{column_name: full_name, validation_class: AsciiType}, {column_name: state, validation_class: AsciiType, index_type: KEYS}, {column_name: birth_date, validation_class: LongType, index_type: KEYS}];

assume users VALIDATOR as ascii;
assume users COMPARATOR as ascii;
assume users KEYS as ascii;
assume users SUB_COMPARATOR as ascii;

set users[bsanderson][full_name] = 'Brandon Sanderson';
set users[bsanderson][birth_date] = 1975;
set users[prothfuss][full_name] = 'Patrick Rothfuss';
set users[prothfuss][birth_date] = 1973;
set users[htayler][full_name] = 'Howard Tayler';
set users[htayler][birth_date] = 1968;

set users[bsanderson][state] = 'UT';
set users[prothfuss][state] = 'WI';
set users[htayler][state] = 'UT';

get users where birth_date = 1973;
get users where state = 'UT';
get users where state = 'UT' and birth_date > 1970;

Labels: noSQL, python, redis

posted by shantanu # Wednesday, July 04, 2012 0 comments

February 10, 2012

Import, Export and SQLize DynamoDB

You can use Hive to export data from DynamoDB to S3 bucket using Hadoop

In order to use hadoop, you need Elastic MapReduce instance. Click on "Create New Job Flow" and use defauls to create a new job called "My Job Flow". You will now get the Master Public DNS Name to connect to using "hadoop" as username. Once connected, type "hive" to get the command prompt where you can link to DynamoDB table.

hive> CREATE EXTERNAL TABLE hivereply (col1 string, col2 string, col3 string, col4 string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "reply",
"dynamodb.column.mapping" = "col1:Id,col2:ReplyDateTime,col3:Message,col4:PostedBy");

You can now use the table hivereply just like any other MySQL table. For e.g.
hive> select * from hivereply where col2 = '2012-01-31 23:09:46';

Amazon DynamoDB#DynamoDB Thread 2 2012-01-31 23:09:46 null

You can call the INSERT OVERWRITE command to write the data to an external directory. You can use this to create an archive of your Amazon DynamoDB data in Amazon S3.

hive> INSERT OVERWRITE DIRECTORY 's3://php-sdk-getting-started-aki-19/' SELECT * FROM hiveTableName;

Labels: aws, noSQL, php

posted by shantanu # Friday, February 10, 2012 0 comments

Using Comparision Operator in dynamodb

If you want to query the data, you need to index it on Range key. For e.g. in the following "reply" table we have a primary key that is combination of Hash key and a Range key. Range key is nothing but the timestamp as on the reply was received. We can write a query that will return all the replies those are older than 7 days.
In this case the column "ReplyDateTime" column is indexed and we can use Comparison Operator.


require_once 'sdk.class.php';

$ten_days_ago = date('Y-m-d H:i:s', strtotime("-10 days"));

// Instantiate the class
$dynamodb = new AmazonDynamoDB();

$add_response = $dynamodb->put_item(array(
    'TableName' => 'reply',
    'Item' => array(
        'Id'            => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ), // Hash Key
        'ReplyDateTime' => array( AmazonDynamoDB::TYPE_STRING => $ten_days_ago                        ), // Range Key
        'Message'       => array( AmazonDynamoDB::TYPE_STRING  => 'DynamoDB Thread 2 Reply 1 text'   ),
        'PostedBy'      => array( AmazonDynamoDB::TYPE_STRING => 'User A'                            ),
    )
));

// Success?
print_r($add_response);


$seven_days_ago = date('Y-m-d H:i:s', strtotime("-7 days"));

$response = $dynamodb->query(array(
    'TableName' => 'reply',
    'HashKeyValue' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ),
// optional parameters
    'AttributesToGet' => array( 'ReplyDateTime', 'Message', 'PostedBy' ),
    'ConsistentRead' => true,
    'RangeKeyCondition' => array(
        'ComparisonOperator' => AmazonDynamoDB::CONDITION_LESS_THAN_OR_EQUAL,
        'AttributeValueList' => array(
            array( AmazonDynamoDB::TYPE_STRING => $seven_days_ago )
        )
    )
));

// 200 response indicates Success
print_r($response);

Labels: aws, noSQL, php

posted by shantanu # Friday, February 10, 2012 0 comments

NoSQL support by AWS

Click on "Create Table" button on "DynamoDB" tab while using AWS web Management Console.

https://console.aws.amazon.com/dynamodb/home

Type table name "testme1" and choose Number as Primary Key Type. Hash attribute name can be "Id". Assuming that you have correctly installed AWS SDK, the following PHP code should add a key - value to the table testme1 table.


require_once 'sdk.class.php';

// Instantiate the class
$dynamodb = new AmazonDynamoDB();

$add_response = $dynamodb->put_item(array(
    'TableName' => 'reply',
    'Item' => array(
        'Id' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ), // Hash Key
        'ReplyDateTime' => array( AmazonDynamoDB::TYPE_STRING => '2012-01-31 23:28:40' ), // Range Key
        'Message' => array( AmazonDynamoDB::TYPE_STRING => 'DynamoDB Thread 2 Reply 1 text' ),
        'PostedBy' => array( AmazonDynamoDB::TYPE_STRING => 'User A' ),
    )
));

// Success?
print_r($add_response);

_____

get item is obviously similar to put_item as shown above:


$get_response = $dynamodb->get_item(array(
    'TableName' => 'reply',
    'Key' => array(
        'HashKeyElement' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' )
    )
));

_____

In order to drop the table, use the following:


$delete_response = $dynamodb->delete_table(array(
    'TableName' => 'reply'
    )
);

_____

The following code will create a table called "reply":


$create_response = $dynamodb->create_table(array(
    'TableName' => 'reply',
    'KeySchema' => array(
        'HashKeyElement' => array(
            'AttributeName' => 'Id',
            'AttributeType' => AmazonDynamoDB::TYPE_STRING
        ),
        'RangeKeyElement' => array(
            'AttributeName' => 'ReplyDateTime',
            'AttributeType' => AmazonDynamoDB::TYPE_STRING
        )
    ),
    'ProvisionedThroughput' => array(
        'ReadCapacityUnits' => 10,
        'WriteCapacityUnits' => 5
    )
));

_____

Here are 3 easy steps you may need to follow if you have not already downloaded and configured Software Development kit from Amazon.

# Download AWS SDK for PHP
svn co http://svn.github.com/amazonwebservices/aws-sdk-for-php.git AWSSDKforPHP
cd AWSSDKforPHP
_____

# copy sample to config file
mv config-sample.inc.php config.inc.php
_____

# add your actual key and secret found on "security credentials" page that can be found here...
# https://aws-portal.amazon.com/gp/aws/securityCredentials

vi config.inc.php

'key' => 'ABCD',
'secret' => 'XYZ',
'default_cache_config' => '/tmp/',

Labels: aws, noSQL, php

posted by shantanu # Friday, February 10, 2012 0 comments

Shantanu's Blog

June 04, 2016

elasticsearch docker image with scripting support

June 03, 2016

Save your packets to elasticsearch

May 23, 2016

load data into elastic using logstash

August 14, 2015

mongoDB mysql comparison cheat sheet

January 24, 2015

query text files

July 04, 2012

Cassandra

February 10, 2012

Import, Export and SQLize DynamoDB

Using Comparision Operator in dynamodb

NoSQL support by AWS

Archives