Saturday, May 10, 2014

Move Big Files to Amazon Cloud

When we want to upload a big file, e.g. a 8G file,to Amazon Cloud storage, it is a good idea to divide the single big file into smaller chunks and upload them one by one. After all chunks are uploaded to Amazon cloud, we can merge them back as a single file.

For example, we have a binary file of about 7.6G, we can split it into 15 chunks. Then we use ftp to upload them to a Linux EC2 Amazon server. As the last step, we merge them back into a single file.

Step 1. Split the file into chunks of 500M.

$ split -b 500m datafile.dat
The above command generates the following 15 smaller files for datafile.dat.
$ ls -ltr
-rw-r--r--+ 1 oradba None  524288000 May  2 18:44 xaa
-rw-r--r--+ 1 oradba None  524288000 May  2 18:44 xab
-rw-r--r--+ 1 oradba None  524288000 May  2 18:45 xac
-rw-r--r--+ 1 oradba None  524288000 May  2 18:46 xad
-rw-r--r--+ 1 oradba None  524288000 May  2 18:46 xae
-rw-r--r--+ 1 oradba None  524288000 May  2 18:47 xaf
-rw-r--r--+ 1 oradba None  524288000 May  2 18:48 xag
-rw-r--r--+ 1 oradba None  524288000 May  2 18:49 xah
-rw-r--r--+ 1 oradba None  524288000 May  2 18:50 xai
-rw-r--r--+ 1 oradba None  524288000 May  2 18:50 xaj
-rw-r--r--+ 1 oradba None  524288000 May  2 18:51 xak
-rw-r--r--+ 1 oradba None  524288000 May  2 18:51 xal
-rw-r--r--+ 1 oradba None  524288000 May  2 18:52 xam
-rw-r--r--+ 1 oradba None  524288000 May  2 18:52 xan
-rw-r--r--+ 1 oradba None  258613760 May  2 18:52 xao
Step 2. Upload those 15 chunks of files to an EBS volume attached to a Linux EC2 server on Amazon.
Step 3. On the Linux EC2 server, merge files into a single file.
$ cat `ls xa*` >> datafile.dat
If you are using Windows, you may install cygwin to take advantage of the above commands.

Friday, May 09, 2014

Oracle Create Table As Select vs SQL Server Select Into

Oracle CTAS (create table as select) is a convenient way to create a new table based on an existing table.

SQL> select * from tbl_test;

       NUM
----------
         1
         2

SQL> create table tbl_test2 as select * from tbl_test;

Table created.

SQL> select * from tbl_test2;

       NUM
----------
         1
         2
In SQL Server, instead of using CTAS, we use "select.. into new_table from old_table" to create a new table based on old table.
select * into tbl_test2 from tbl_test;