Porting flat file to MariaDB/MySQL

Example of how to port a flat file to MariaDB/MySQL.

Sample data of the data.

MariaDB [(none)]> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

Create a blockchain table to host the data and verifying the table spec.

MariaDB [mysql]> create table blockchain (

    -> akey varchar(300),

    -> bvalue varchar(300)

    -> );

Query OK, 0 rows affected (0.08 sec)

MariaDB [mysql]> select count(*) from blockchain;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

MariaDB [mysql]> desc blockchain;

+--------+--------------+------+-----+---------+-------+

| Field  | Type         | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| akey   | varchar(300) | YES  |     | NULL    |       |

| bvalue | varchar(300) | YES  |     | NULL    |       |

+--------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Load the data file with INFILE. Make sure the flat file resides in the correct location.

MariaDB [mysql]> load data infile '/home/xxxxx/datafile/data_output.txt' into table blockchain fields terminated by  ':';

Data are loading into the table.

MariaDB [mysql]> select count(*) from blockchain;

+----------+

| count(*) |

+----------+

|     9493 |

+----------+

1 row in set (0.00 sec)

MariaDB [mysql]> select * from blockchain limit 2;

+-----------------------------------------------------------------------+---------------------------------------------------------------------+

| akey                                                                  | bvalue                                                              |

+-----------------------------------------------------------------------+---------------------------------------------------------------------+

| 0x01000000010000000000000000000000000000000000000000030A000000000000  |  0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0 |

| 0x01000000010000000000000000000000000000000000000000100A000000000000  |  0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0 |

+-----------------------------------------------------------------------+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

Verifying the relationship.

MariaDB [mysql]> select bvalue from blockchain where akey = '0x01000000010000000000000000000000000000000000000000030A000000000000';

+---------------------------------------------------------------------+

| bvalue                                                              |

+---------------------------------------------------------------------+

|  0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0 |

+---------------------------------------------------------------------+

1 row in set (0.00 sec)

That's it.