Skip to main content

Posts

Showing posts from 2013

psql commands

  \d [NAME]      describe table, index, sequence, or view   \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)                  list tables/indexes/sequences/views/system tables   \da [PATTERN]  list aggregate functions   \db [PATTERN]  list tablespaces (add "+" for more detail)   \dc [PATTERN]  list conversions   \dC            list casts   \dd [PATTERN]  show comment for object   \dD [PATTERN]  list domains   \df [PATTERN]  list functions (add "+" for more detail)   \dg [PATTERN]  list groups   \dn [PATTERN]  list schemas (add "+" for more detail)   \do [NAME]     list operators   \dl            list large objects, same as \lo_list   \dp [PATTERN]...

postgresql dump and restore

sudo -u postgres createuser -D -A -P myuser sudo -u postgres createdb -O myuser mydb pg_dump -i -h  localhost -p 5432 -U myuser -f "/tmp/mydb.sql" mydb --schema=mydb_schema psql -h localhost -p 5432 -U myuser -d mydb < mydb.sql pg_dump -i -h localhost -p 5432 -U myuser -F c -b -v -f "/tmp/mydb.backup" mydb --schema=mydb_schema pg_restore -i -h localhost -p 5432 -U myuser -d mydb -v "/tmp/mydb.backup"

postgresql tricks

Tables are automatically dropped ; select 'drop table "' || tablename || '" cascade;'  from pg_tables where schemaname = 'public'; Sequences are automatically dropped ; SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c where relname like 'myprefix%' Show the current database select current_database(); Show backend server version select version(); Show the current user select current_user; Show the session user select session_user; kill an association SELECT  pg_terminate_backend(procpid) FROM pg_stat_activity   WHERE procpid <> pg_backend_pid();  

PostgreSQL Hot Standby Replication

#Install postgres in the primary and standby server as usual root# wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm #install the repo root# rpm -i pgdg-centos91-9.1-4.noarch.rpm #We now need to edit the CentOS-Base.repo to exclude postgre.Simply edit CentOS-Base.repo and add 'exclude=postgresql*'  to the [base] and [updates] sections: root# cd /etc/yum.repos.d root# vi CentOS-Base.repo     [base]     name=CentOS-$releasever - Base     mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os     #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/     gpgcheck=1     gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6     exclude=postgresql*     #released updates     [updates]     name=CentOS-$releasever - Updates     mirrorlist=http://mirrorlist.cento...

Check to size of collection in MongoDB

db.collection.dataSize(): data size for the collection. db.collection.storageSize(): allocation size, including unused space. db.collection.totalSize(): the data size plus the index size. db.collection.totalIndexSize(): the index size. PRIMARY> mdb = db.getSiblingDB("myDbName") PRIMARY> mdb.getCollectionNames().forEach(function(c) { s = mdb[c].stats(); printjson(s)} )

Selected data copy to an other collection

PRIMARY> db.orders.find().forEach(printjson) {  "_id" : ObjectId("513466b691390735f2237119"),  "category" : "blue",  "recordDate" : ISODate("2012-04-02T17:04:11.105Z"),  "status" : "A",  "price" : 50 } {  "_id" : ObjectId("513466e591390735f223711a"),  "category" : "red",  "recordDate" : ISODate("2012-04-02T10:05:11.105Z"),  "status" : "A",  "price" : 60 } {  "_id" : ObjectId("5134689291390735f223711b"),  "category" : "red",  "recordDate" : ISODate("2012-04-02T10:05:11.105Z"),  "status" : "A",  "price" : 60 } PRIMARY> var docs = db.orders.find({ "category" : "red"}); PRIMARY> db.orders.find().forEach(printjson) {  "_id" : ObjectId("513466e591390735f2...

Property Loader

loading property and configuration files in Java   import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.util.Properties; public class LoadProperties {        private static LoadProperties instance = null;         private Properties properties = new Properties();         public static void main(String[] args) {         LoadProperties instance = LoadProperties.getInstance();         instance.testProperties();     }    protected LoadProperties() {        FileReader fileReader;         try {             fileReader = new FileReader(new File("/home/ercan/config.properties"));            ...

Postgresql Test Data

SELECT generate_series(1,1000) as key ,(random()*10000.)::numeric(6,2) ,repeat('5',(random()*20)::integer) ,substr('abcdefghijklmnopqrstuvwxyz',1, (random()*26)::integer) ,(ARRAY['one','two','three'])[1+random()*3] ,(random()*(9*10^18))::bigint ;

vi tips and tricks

    Command Summary *    find cursor under word STARTING vi      vi filename    edit a file named "filename"      vi newfile     create a new file named "newfile" ENTERING TEXT      i            insert text left of cursor      a            append text right of cursor MOVING THE CURSOR      h            left one space      j            down one line      k            up one line      l            right one space BASIC EDITING ...

PostgreSQL before insert Trigger

CREATE FUNCTION update_person_operation_date() RETURNS TRIGGER AS $$ BEGIN     UPDATE person SET operation_date=now() WHERE person_id=NEW.person_id;     RETURN NEW; END; $$ LANGUAGE plpgsql; -- then CREATE TRIGGER update_person_operation_date_trigger BEFORE INSERT ON person FOR EACH ROW EXECUTE update_person_operation_date;

MongoDB $where operator sample

@Test     public void testWhereOperator(){                                                                                                                                                                                           DBCollection collection = MongoDBFactory.getCollection("test","orders"); BasicDBObject query = new BasicDBObject(); query.put("cust_id", "abc124"); query.put("status", "A"); query.put("$where", "this.price > 50"); final String code = "var i = 0;" +     ...

Aggregation method with Mongo java driver

PRIMARY> db.orders.find().forEach(printjson) { "_id" : ObjectId("513466b691390735f2237119"), "category" : "blue", "recordDate" : ISODate("2012-04-02T17:04:11.105Z"), "status" : "A", "price" : 50 } { "_id" : ObjectId("513466e591390735f223711a"), "category" : "red", "recordDate" : ISODate("2012-04-02T10:05:11.105Z"), "status" : "A", "price" : 60 } { "_id" : ObjectId("5134689291390735f223711b"), "category" : "red", "recordDate" : ISODate("2012-04-02T10:05:11.105Z"), "status" : "A", "price" : 60 } java test code :  @Test     public void testAggregation(){                                                                                                             ...

MongoDB update embedded objects with javascript

db.colors.insert({   "_id" : 12,   "records" : [{       "blue" : 1,       "green" : 22,       "yellow" : 26     }, {       "blue" : 1,       "green" : 22,       "yellow" : 44     }, {       "blue" : 1,       "green" : 28,       "yellow" : 26     }, {       "blue" : 1,       "green" : 33,       "yellow" : 26     }] }) db.colors.find({ "records.blue" : 1}).forEach(function(x) {     printjson(x.records)    }) db.colors.find({ "records.blue" : 1}).forEach(function(x) {   match = false; for(i=0 ; i< x.records.length ; i++) {     var blueValue = x.records[i].blue;   //print(blueValue);   if(blueValue==1) {       print("green--yellow value : " + x.records[i].green ...

MongoDB Batch Insert

Boolean returnValue=false; DBCollection collUser = MongoDBFactory.getCollection("MySchema","MyColl"); List<BasicDBObject> entries = new ArrayList<BasicDBObject>(); for (User user : userList) {         BasicDBObject docUser = new BasicDBObject();     docUser.put("_id",user.get_id());     docUser.put("userId", user.getUserId());     docUser.put("name", user.getName());     docUser.put("surname", user.getSurName());     docUser.put("age", user.getAge());           entries.add(docUser); }     try { collUser.insert(entries.toArray(new DBObject[entries.size()]),WriteConcern.SAFE);     returnValue=true; } catch (MongoException e) { e.printStackTrace(); }

Hadoop cluster installation and demos

sudo apt-get purge openjdk* sudo apt-get install python-software-properties #install oracle jdk java -version sudo addgroup hadoop sudo adduser --ingroup hadoop hduser (hadoop-master,hadoop-slave1,hadoop-slave2) sudo vim /etc/sysctl.conf # disable ipv6 net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.conf.lo.disable_ipv6 = 1 (hadoop-master,hadoop-slave1,hadoop-slave2) vim /etc/hosts 10.1.1.111      hadoop-master       hadoop-master.mydomain.com    hadoop-master 10.1.1.112      hadoop-slave1       hadoop-slave1.mydomain.com    hadoop-slave1 10.1.1.113      hadoop-slave2       hadoop-slave2.mydomain.com    hadoop-slave2 (hadoop-master) #Configuring SSH su - hduser ssh-keygen -t rsa -P "" cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys ssh hadoop-master.mydomain.com (hadoop-master) Distribut...

Usage of Apache Sqoop and Samples

hduser@my_hadoop_domain:/usr/local/sqoop/conf$ sqoop import --connect jdbc:mysql://mysql_db_url/MY_SCHEMA --table MY_TABLE --username myDbUser --password myDbUserPass --target-dir dest #or hduser@my_hadoop_domain:/usr/local/sqoop/conf$ sqoop ---connect jdbc:mysql://mysql_db_url/MY_SCHEMA --table MY_TABLE --username myDbUser --password myDbUserPass --as-sequencefile hduser@my_hadoop_domain:/usr/local/sqoop/docs$ hdfs -ls Found 5 items drwxr-xr-x   - hduser supergroup          0 2013-01-31 15:41 /user/hduser/MY_TABLE drwxr-xr-x   - hduser supergroup          0 2013-01-24 09:51 /user/hduser/books drwxr-xr-x   - hduser supergroup          0 2013-01-31 15:15 /user/hduser/dest drwxr-xr-x   - hduser supergroup          0 2013-01-24 14:13 /user/hduser/examples drwxr-xr-x   - hduser supergroup          0 2013-01-24 10:48 /user/hduser/res...

Install Apache Sqoop

SQOOP2 (1.99.1) installation wget http://www.eu.apache.org/dist/sqoop/1.99.1/sqoop-1.99.1-bin-hadoop100.tar.gz cd /usr/local/ && sudo tar \-zxvf /home/hduser/sqoop-1.99.1-bin-hadoop100.tar.gz mv sqoop-1.99.1-bin-hadoop100/ sqoop chown -Rf hduser:hadoop sqoop vim /home/hduser/.bashrc export SQOOP_HOME=/usr/local/sqoop if [ -d "$SQOOP_HOME/bin" ] ; then    PATH=$PATH:$SQOOP_HOME/bin fi # INSTALLING DEPENDENCIES hduser@my_hadoop_domain:/usr/local/sqoop$ ./bin/addtowar.sh -hadoop-version 1.1.1 -hadoop-path /usr/local/hadoop/ cd /home/hduser/jars wget http://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.21/mysql-connector-java-5.1.21.jar # You can add any arbitrary java jar file to Sqoop server using script addtowar.sh with -jars parameter hduser@mypc:/usr/local/sqoop/bin$ ./bin/addtowar.sh -jars /home/hduser/jars/mysql-connector-java-5.1.21.jar hduser@my_hadoop_domain:/usr/local/sqoop$ ./bin/sqoop.sh server start hduser@my_hadoop_domain:/usr/loc...

Hadoop Mongo Map Reduce sample

create TestHadoop collection in test schema and insert 2M doc. myserver:PRIMARY> use test myserver:PRIMARY> for(i=0;i<1000000;i++) { db.TestHadoop.insert({"name":"ercan","number":i})} myserver:PRIMARY> for(i=0;i<1000000;i++) { db.TestHadoop.insert({"name":"can","number":i})} cd /home/hduser/mongo_hadoop mkdir -p myprojects/test_hadoop cd myprojects/test_hadoop mkdir -p  src/main/resources cd src/main/resources vim mongo-test_hadoop.xml <?xml version="1.0"?> <configuration>   <property>     <!-- run the job verbosely ? -->     <name>mongo.job.verbose</name>     <value>true</value>   </property>   <property>     <!-- Run the job in the foreground and wait for response, or background it? -->     <name>mongo.job.background</name>     <value>false</value>   </property> ...

mysql query result export to csv via python

#!/usr/bin/env python import MySQLdb import csv HOST = "myshost" USER = "myuser" NAME = "myDB" PASSWD = "mypass" PORT = "myport" def connect_db(host, port, user, password, db):     try:         return MySQLdb.connect(host=host, port=port, user=user, passwd=password, db=db)     except MySQLdb.Error, e:         sys.stderr.write("[ERROR] % d: % s\n" % (e.args[0], e.args[1]))         return False def main():     QUERY="SELECT FIELD1,FIELD2,FIELD3 FROM MY_TABLE"     FILENAME="/home/ercan/dumps/dump.csv"       db = connect_db(HOST,int(PORT),USER,PASSWD,NAME)     dump_writer = csv.writer(open(FILENAME,'w'), delimiter=',',quotechar="'")     cur = db.cursor()     cur.execute(QUERY)     for row in cur.fetchall() :         dump_writer.writerow(row)       print("mysql query result e...

Installation Pig and using with Hadoop

wget http://www.eu.apache.org/dist/pig/pig-0.10.0/pig-0.10.0.tar.gz sudo cp pig-0.10.0.tar.gz /usr/local/ sudo tar -xzvf pig-0.10.0.tar.gz sudo mv pig-0.10.0/ pig/ vim /home/hduser/.bashrc export PIG_HOME=/usr/local/pig if [ -d "$PIG_HOME/bin" ] ; then    PATH=$PATH:$PIG_HOME/bin fi chown -R hduser:hadoop /usr/local/pig/ vim /home/hduser/categories.csv 1 routines 2 services 3 applications vim /home/hduser/products.csv 1 AdminConsole 2 LogService 3 CustomerService hadoop dfs -put /home/hduser/categories.csv examples/categories.csv hadoop dfs -put /home/hduser/categories.csv examples/products.csv hduser@myhost://home/hduser$ pig 13/01/24 14:14:14 WARN pig.Main: Cannot write to log file: /home/hduser/pig_1359029654768.log 2013-01-24 14:14:14,773 [main] INFO  org.apache.pig.Main - Apache Pig version 0.10.0 (r1328203) compiled Apr 19 2012, 22:54:12 2013-01-24 14:14:14,968 [main] INFO  org.apache.pig.backend.hadoop.executioneng...

Installation Hive on Hadoop

wget http://ftp.itu.edu.tr/Mirror/Apache/hive/hive-0.10.0/hive-0.10.0-bin.tar.gz sudo cp hive-0.10.0-bin.tar.gz /usr/local/ cd /usr/local/ sudo tar -xzvf hive-0.10.0-bin.tar.gz sudo mv hive-0.10.0-bin/ hive/ vim /home/hduser/.bashrc export HIVE_HOME=/usr/local/hive if [ -d "$JAVA_HOME/bin" ] ; then    PATH=$PATH:$HIVE_HOME/bin fi mkdir /usr/local/warehouse chown -R hduser:hadoop hive/ chown -R hduser:hadoop warehouse/ cd /usr/local/warehouse hduser@myhost:/usr/local/warehouse$ hive hive> Ctrl+z vim /home/hduser/hive_examples.csv 1 apache 2 httpd 3 warehouse 4 java hadoop dfs -mkdir examples hadoop dfs -ls . Found 3 items drwxr-xr-x   - hduser supergroup          0 2013-01-24 09:51 /user/hduser/books drwxr-xr-x   - hduser supergroup          0 2013-01-24 12:50 /user/hduser/examples drwxr-xr-x   - hduser supergroup          0 2013-01-24 10:48 ...

Install Hadoop Single Node Cluster and Configuration

sudo apt-get install python-software-install #Hadoop requires Java 1.6+. It is built and tested on Oracle (nee Sun) Java, which is the only "supported" JVM. #Go to this page http://www.oracle.com/technetwork/java/javase/downloads/jdk6u38-downloads-1877406.html   downloaded  jdk-6u38-linux-x64.bin chmod a+x jdk-6u38-linux-x64.bin ./jdk-6u38-linux-x64.bin #During installation it will ask you to register - press ENTER. #JDK 6 package is extracted into ./jdk1.6.0_38 directory, for example ./jdk1.6.0_30. mv jdk1.6.0_38 java-6-oracle #if /usr/lib/jvm not exists sudo mkdir /usr/lib/jvm sudo mv java-6-oracle /usr/lib/jvm/ #if ssh not installed sudo apt-get install ssh sudo apt-get install rsync java -version sudo addgroup hadoop sudo adduser --ingroup hadoop hduser #Configuring SSH su - hduser ssh-keygen -t rsa -P "" cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys ssh localhost sudo vim /etc/sysctl.conf # disable ipv6 net.ip...

Save a document with pymongo

import sys from datetime import datetime from pymongo import Connection from pymongo.errors import ConnectionFailure """ Connect to mongodb """ def connect():     try:         c = Connection(host="localhost", port=27017)     except ConnectionFailure, e:         sys.stderr.write("Could not connect to MongoDB: %s" % e)         sys.exit(1)     return c """ main method """ def main():     c = connect()     db = c["test"]     user_doc = {         "username" : "ercan.can",         "firstname" : "ercan",         "surname" : "can"     }     db.users.insert(user_doc, safe=True)     print "Successfully inserted document: %s" % user_doc if __name__ == "__main__":     main()

python pip, virtualenv, django, pymongo installation

$sudo apt-get install python-setuptools python-dev build-essential $sudo easy_install pip $sudo pip install virtualenv $mkdir -p ~/virtualfiles $cd ~/virtualfiles $virtualenv -p python2.7 virtualenv $source ~/virtualfiles/virtualenv/bin/activate (virtualenv)ercan@mypcname:~/virtualfiles$ pip install django (virtualenv)ercan@mypcname:~/virtualfiles$ django-admin.py startproject myproject (virtualenv)ercan@mypcname:~/virtualfiles$ python manage.py startapp myapp (virtualenv)ercan@mypcname:~/virtualfiles$ python manage.py runserver localhost:8000 (virtualenv)ercan@mypcname:~/virtualfiles$ python -c "import django; print(django.get_version())" (virtualenv)ercan@mypcname:~/virtualfiles$ sudo pip install pymongo

my vimrc

vim /home/ercan/.vimrc syntax on set background=dark set ruler                     set more                     set autoread                 set number                   set hidden set noautowrite             set lazyredraw               set showmode set showcmd set nocompatible             set autoindent smartindent   set smarttab                 set tabstop=2                 set shiftwidth=2 set scrolloff=5             set sidescrolloff=5           set history=200 set backspace=indent,eol,start set linebreak set cmdheight=2       ...

MONGO update and delete queries

PRIMARY> var query = {{name: 'ercan'} PRIMARY> db.users.remove(query) PRIMARY> db.users.update({score: 1}, {$set: {name: 'ercan', dob: new Date('Dec 17, 1999'), likes: ['sport'], gender: 'm', score: 9}},{ upsert: true }) PRIMARY> db.users.find()         { "_id" : ObjectId("50eeef9f6745413ec3aab73e"), "score" : 9, "dob" : ISODate("1981-12-16T21:00:00Z"), "gender" : "m", "likes" : [ "sport" ], "name" : "ercan" } PRIMARY> db.users.update({score: 9}, {$set: {name: 'ercan', dob: new Date(1978, 12, 18, 15, 12), likes: ['sky'], gender: 'm', score: 9}}) PRIMARY> db.users.find() { "_id" : ObjectId("50eeef9f6745413ec3aab73e"), "score" : 9, "dob" : ISODate("1979-01-18T11:12:00Z"), "gender" : "m", "likes" : [ "sky...

mongo installation : gpg key error

$ sudo apt-key adv --keyserver keyserver.ubuntu.com --recv 7F0CEB10 Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /tmp/tmp.0OmgpQGnPi --trustdb-name /etc/apt/trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyserver keyserver.ubuntu.com --recv 7F0CEB10 gpg: requesting key 7F0CEB10 from hkp server keyserver.ubuntu.com ?: keyserver.ubuntu.com: Connection timed out gpgkeys: HTTP fetch error 7: couldn't connect: Connection timed out gpg: no valid OpenPGP data found. gpg: Total number processed: 0 The hkp protocol runs on port TCP 11371 , firewall allows access to that port on keyserver.ubuntu.com.