MySQL: SELECT / LOAD in Batch

Here's are the MySQL commands to extract data from  table and to load data into tables.
Unlike other DBMS, MySQL offers SELECT..INTO.. and LOAD DATA.. commands are quite faster and convenient. Data would be fetched and loaded in bacthes.


Sample query to extract data from a table:

SELECT *
FROM tmp.my_table
INTO OUTFILE '/file_path/filename.csv'
FIELDS TERMINATED BY '|$|'
LINES TERMINATED BY '\n';

You can select particular columns, instead of  '*' .

OUTFILE should be given with file path and name. By default, file will be created inside data directory.
Important thing to be noted here is, the file will be generated inside the local host only.
Means, if you're executing above query on server S2, fetching data from S1, the file will be generated inside S1 itself.

Also, to execute above command, it requires FILE Privilege for MySQL user and the destination directory should also be having server level MySQL user privilege.

Below is the query that an be used to load data in batches:

LOAD DATA LOCAL 
INFILE '/file_path/filename.csv' 
INTO TABLE tmp.my_table 
CHARACTER SET utf8 
FIELDS TERMINATED '|$|' 
LINES TERMINATED BY '\n';

You can use, 'IGNORE INTO' instead of 'INTO', to ignore the duplicate records.
CHARACTER SET is mentioned explicitly as the data being loaded is having local languages(like kannada, hindi, telugu,..). Without this, data won't be visible - scrambled.

Linux: Concatenate In Linux

Here is the commands to concatenate in Linux.

Consider a file as below:

[root@myserver 6048]# cat test.txt
12
0
123445456
234565443
3883
33

My requirement is to generate update/delete statement for each of this value. Think of a file with millions of records.

My update statement would be:

update mytable set col1='Yes' where col2=' value in the file';

Here's the solution:

[root@myserver 6048]# cat test.txt | sed "s/^/update mytable set col1='Yes' where col2='/g;s/$/';/g"
update mytable set col1='Yes' where col2='12';
update mytable set col1='Yes' where col2='0';
update mytable set col1='Yes' where col2='123445456';
update mytable set col1='Yes' where col2='234565443';
update mytable set col1='Yes' where col2='3883';
update mytable set col1='Yes' where col2='33';
update mytable set col1='Yes' where col2='';
update mytable set col1='Yes' where col2='987676';


You may redirect the output to some other file, as below:

[root@myserver 6048]# cat test.txt | sed "s/^/update mytable set col1='Yes' where col2='/g;s/$/';/g" > updates_test.sql


You may also use the below query for the purpose:


[root@myserver 6048]# sed  "s/^/update mytable set col1='Yes' where col2='/g;s/$/';/g"  test.txt > updates_test.sql


Ping me for any doubts.. +Kiran Yadagere  | facebook.com/kiranyadagere

Linux: Adding Numbers in a File

Here's a Linux command to add numbers inside a file:


[root@myserver misc]# cat num.test
1
2
3
4
5


This is a sample file with only 5 lines. Think of a file with millions of lines. Either you can do with MS - Excel, which may hang(!!) or use Linux command - 'awk'- which is powerful and easier:

[root@myserver misc]# awk '{s+=$1} END {print s}' num.test
15

As simple as that..!!

MongoDB: mongoexport Command - Sample

Here's is a simple MongoDB 'mongoexport' command syntax to backup the document/database. This command can be used to backup based on particular conditions ('where' clause) -

Go to MongoDB bin  directory and execute as below:

./mongoexport --host localhost --port 10000 -d doc_name -c coll_name -q '{"_id":"12345687899asdc"}' -o /path/file_name.csv

This will export a single record which is matching the given value for field '_id'.

Android: Read Kannada/Other Local Languages in your mobile

Here's how to Read Kannada, other Indian languages or any Unicode characters in your Andriod mobile:

(Mainly for Facebook ..!!)

This one is mainly for old android sets which doesn't have support for Unicode.  All today's Andriod mobiles display it correctly, by default.

Here's the steps what I followed to read Kannada on my Android phone:

1. Download and install 'Opera Mini' from Android Market/Google Play Store

2. Open Opera Mini and type below words in address bar, hit enter:

about:config

3. This will open a settings page. Scroll down to until you see below line:

Use bitmap fonts for complex scripts

4. There should be a drop down list, select 'Yes' and Save it.

5. Now open new tab and test it. You can type below address, which is a kannada website:

sampada.net

You should be able to see Kannada letters, clearly.

Geek: Write Kannada/Other Local Languages

How to write Kannada/Hindi or any other other language in any Website:

  

There are many ways, but here is one of the easiest way. (Its for PC/Laptop, doesn't work with mobile)

Follow the steps below:

1. Download and install Mozilla Firefox browser using below link if you don't have already:

http://www.mozilla.org/en-US/firefox/new/

2. Open Firefox and press Alt to see the menu bar. Go to Tools -> Add-ons.

Or

Press Ctrl + Shift + A

3. It opens a new tab 'Add-ons Manager'. In upper right corner there will be a Search bar. Type 'Lipikaar' and press Enter.

4. It will search for an add on - 'Lipikaar' (see the attached image). Now click on Install button. It prompts a dialogue box - click on Accept Install.

It downloads the add-on of 176 KB.

  

5. After installation, click on Restart Now.

6. After restarting, in the same 'Add-on Manager' tab, click on 'Extentions' (see the image). It shows all the installed add-ons. In Lipikaar, click on Options button.

Select your preferred language and click OK (see the image).

7. Last but important step is, whenever you want to type in the language you seleced in Lipikaar, press Ctrl + Alt + L .

Use the same to revert back to english.

Any doubts?!

Geek: Download Video from Web (YouTube)

So, here comes an interesting topic.

How to download videos from any websites(not only YouTube)?

There are many ways. Below is one of the easiest way:

For this, Mozilla Firefox comes to our help. Using an add-on called 'Video download helper'

1. Download and install Mozilla Firefox browser using below link if you don't have already:

http://www.mozilla.org/en-US/firefox/new/

2. Open Firefox and press Alt to see the menu bar. Go to Tools -> Add-ons.

Or

Press Ctrl + Shift + A

3. It opens a new tab 'Add-ons Manager'. In upper right corner there will be a Search bar. Type 'Video Download Helper' and press Enter.

4. It will search for an add on - 'Video DownloadHelper' (see the attached image). Now click on Install button. It prompts a dialogue box - click on Accept Install.

It downloads the add-on of 756 KB.

5. After installation, click on Restart Now.

6. After restarting, in the same 'Add-on Manager' tab, click on 'Extentions'. It shows all the installed add-ons. In DownloadHelper, click on Options button.

Click on 'Services -> Download -> Storage Directory' to set the download location and click OK (see the image).

7. Now open any website and play a video. As the video starts playing, on the left top corner, just before the address bar, the Video Download Helper icon starts rotating. Click on the drop down arrow of it which gives you options to download i different formats.

I would suggest to click on .mp4 format. Select Quick Download from it.(See the image).

8. Your fav video starts downloading now. No need to keep playing the video.

But don't close Mozilla Firefox.