Just another WordPress.com site

Posts tagged ‘mysql’

mysql: Create external table using CSV engine and file

One thing that the CSV engine allows for is the instantaneous data transfer from flat files to information accessible via SQL in MySQL. For example, imagine having the need to load a file containing 5 million records into a MySQL table that uses the MyISAM storage engine:

mysql> desc client_detail;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| client_transaction_id | decimal(22,0) | YES  |     | NULL    |       |
| transaction_timestamp | datetime      | YES  |     | NULL    |       |
| transaction_comment   | varchar(30)   | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> load data infile '/usr/local/mysql519/data/gim/flatdata.dat' 
    ->  into table client_detail
    ->  fields terminated by ',';
Query OK, 5023575 rows affected, 0 warnings (27.38 sec)
Records: 5023575  Deleted: 0  Skipped: 0  Warnings: 0

While MyISAM accepts the data pretty quickly, you can make all the data in the flat file instantly available to MySQL in the following manner. First, create a CSV table that mirrors the format of the incoming flat file:

mysql> create table client_detail_csv (client_transaction_id decimal(22,0),
    -> transaction_timestamp datetime, transaction_comment varchar(30))
    -> engine=csv;
Query OK, 0 rows affected (0.01 sec)

Then, go to the operating system and simply rename the operating system flat file to the file MySQL created for the new CSV table:

[mysql@linux1 ~]$ cd /usr/local/mysql519/data/gim

[mysql@linux1 gim]$ ls -l
total 785848
-rw-rw----  1 mysql mysql        35 May  1 06:54 client_detail_csv.CSM
-rw-rw----  1 mysql mysql         0 May  1 06:54 client_detail_csv.CSV
-rw-rw----  1 mysql mysql      8718 May  1 06:54 client_detail_csv.frm
-rw-rw----  1 mysql mysql      8718 May  1 05:56 client_detail.frm
-rw-rw----  1 mysql mysql 221037300 May  1 06:00 client_detail.MYD
-rw-rw----  1 mysql mysql      1024 May  1 06:00 client_detail.MYI
-rw-rw----  1 mysql mysql 291367350 May  1 05:55 flatdata.dat

[mysql@linux1 gim]$ mv flatdata.dat client_detail_csv.CSV

The rename operation occurs instantly as nothing data-wise changes at the operating system level. At that point, MySQL has all the data available to it:

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from client_detail_csv;
+----------+
| count(*) |
+----------+
|  5023575 |
+----------+
1 row in set (16.17 sec)

Thus, you’ve effectively loaded 5 million records instantaneously into MySQL. The great thing is the same effect would be experienced if the file contained 10 million, 20 million, or 100 million records.

Advertisements

P5.0.4 / WP3.6.1 Tutorial | Pydio, formerly AjaXplorer

P5.0.4 / WP3.6.1 Tutorial Recently Tested With

Contents

  1. Basic installation
  2. WordPress Side
  3. Pydio Side
  4. Result

Basic installation

  1. First install WordPress
  2. Install pydio with Mysql database
  3. Check both Wp and pydio has admin login.(Same admin username is preferred)
  4. I do not know what is the exact problem but I had some problems with different admin usernames maybe some browser cache problems.

WordPress Side

  1. Login to admin panel in WordPress and install pydio_cms_4.0.1 via Plugins/Addnew option
  2. Be aware that the original file you downloaded from sourceforge.net has all the cms plugins.So extract that file and select the directory wordpress/ajaxplorer only.You can either zip it again and upload to wordpress or copy the whole directory (only AjaXplorer in the wordpress dir) to wp-content/plugins directory.Warning !!! Be sure that there is no recurring directories otherwise the plugin install will return with error.
  3. Activate the plugin.
  4. On the dashboard select Settings/AjaXplorer
  5. In the Ajaxplorer path enter the full installation path something like : /home/user/public_html/ajaxplorer
  6. Choose a secret key
  7. Be sure that Auto Create (Create Ajxp users when they login) option is Checked to Yes.And do not forget to save changes.
  8. We finished the WordPress side.Now it’s time for Pydio part.

To Avoid Google’s duplicate Policy Please visit the complete tutorial at http://ecarobar.com/p5-0-4-wp3-6-1-tutorial-pydio-formerly-ajaxplorer-2/

%d bloggers like this: