MySQL – Using “Load Data” Statement

Background

Let us load data into our MySQL using it’s native “Load Data” statement.

Outline

  1. Data Source
    • Identify Data Source
  2. Review Data
  3. Download Data
    • Tools
      • CURL
  4. Prepare Target Storage
    • Create Tables
      • Create Staging Table
      • Create Actual Table
  5. Load Data
    • Load data into staging table
    • Move data from staging table into actual table

Tasks

Data Source

Identify Data Source

The easiest and most familiar data from me is the bible.

Outline

  1. Data Element
    • Organization
      • scrollmapper
    • Repository
      • scrollmapper / bible_databases
        Link
    • Text
      • Text / KJV
        Link

        • Genesis
          • KJV
            Link
          • 1 Genesis – King James Version (KJV).txt ( HTML )
            Link
          • 1 Genesis – King James Version (KJV).txt ( RAW )
            Link

Review Data

Here is what the data looks like.

Image

Textual


Genesis - King James Version (KJV)
[1:1] In the beginning God created the heaven and the earth.
[1:2] And the earth was without form, and void; and darkness was upon the face of the deep. And the Spirit of God moved upon the face of the waters.
[1:3] And God said, Let there be light: and there was light.
[1:4] And God saw the light, that it was good: and God divided the light from the darkness.
[1:5] And God called the light Day, and the darkness he called Night. And the evening and the morning were the first day.
[1:6] And God said, Let there be a firmament in the midst of the waters, and let it divide the waters from the waters.
[1:7] And God made the firmament, and divided the waters which were under the firmament from the waters which were above the firmament: and it was so.
[1:8] And God called the firmament Heaven. And the evening and the morning were the second day.
[1:9] And God said, Let the waters under the heaven be gathered together unto one place, and let the dry land appear: and it was so.
[1:10] And God called the dry land Earth; and the gathering together of the waters called he Seas: and God saw that it was good.
[1:11] And God said, Let the earth bring forth grass, the herb yielding seed, and the fruit tree yielding fruit after his kind, whose seed is in itself, upon the earth: and it was so.
[1:12] And the earth brought forth grass, and herb yielding seed after his kind, and the tree yielding fruit, whose seed was in itself, after his kind: and God saw that it was good.
[1:13] And the evening and the morning were the third day.
[1:14] And God said, Let there be lights in the firmament of the heaven to divide the day from the night; and let them be for signs, and for seasons, and for days, and years:
[1:15] And let them be for lights in the firmament of the heaven to give light upon the earth: and it was so.
[1:16] And God made two great lights; the greater light to rule the day, and the lesser light to rule the night: he made the stars also.
[1:17] And God set them in the firmament of the heaven to give light upon the earth,
[1:18] And to rule over the day and over the night, and to divide the light from the darkness: and God saw that it was good.
[1:19] And the evening and the morning were the fourth day.
[1:20] And God said, Let the waters bring forth abundantly the moving creature that hath life, and fowl that may fly above the e

 

Download Data

Tools

Here are tools for downloading data from a website.

Outline

Out of the following tools, please choose one:-

  1. wget
  2. curl

Tools

wget
Syntax

sudo wget -O [filename]  [url] 

 

Sample

sudo wget -O "/var/lib/mysql-files/bible.kjv.genesis.txt" "https://raw.githubusercontent.com/scrollmapper/bible_databases/master/txt/KJV/1%20Genesis%20-%20King%20James%20Version%20(KJV).txt" 

 

Output – Image

Output – Text
>sudo wget -O "/var/lib/mysql-files/bible.kjv.genesis.txt" "https://raw.githubusercontent.com/scrollmapper/bible_databases/master/txt/KJV/1%20Genesis%20-%20King%20James%20Version%20(KJV).txt"
[sudo] password for dadeniji:
--2020-10-22 08:12:45-- https://raw.githubusercontent.com/scrollmapper/bible_databases/master/txt/KJV/1%20Genesis%20-%20King%20James%20Version%20(KJV).txt
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.40.133
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.40.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 208409 (204K) [text/plain]
Saving to: ‘/var/lib/mysql-files/bible.kjv.genesis.txt’

/var/lib/mysql-files/bible.kjv.genesis.tx 100%[=====================================================================================>] 203.52K --.-KB/s in 0.08s

2020-10-22 08:12:46 (2.65 MB/s) - ‘/var/lib/mysql-files/bible.kjv.genesis.txt’ saved [208409/208409]

>

 

 

curl
Syntax

sudo curl -o [filename]  [url] 

 

Sample

sudo curl "https://raw.githubusercontent.com/scrollmapper/bible_databases/master/txt/KJV/1%20Genesis%20-%20King%20James%20Version%20(KJV).txt" -o "/var/lib/mysql-files/bible.kjv.genesis.txt"

 

Output – Image

 

Output – Text

>sudo curl -o "/var/lib/mysql-files/bible.kjv.genesis.txt" "https://raw.githubusercontent.com/scrollmapper/bible_databases/master/txt/KJV/1%20Genesis%20-%20King%20James%20Version%20(KJV).txt"
[sudo] password for dadeniji:
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 203k 100 203k 0 0 272k 0 --:--:-- --:--:-- --:--:-- 272k
>

 

Prepare Target Storage

Data Model

Create Tables

Outline

  1. Create Database Tables
    • Create staging Table
    • Create actual Table

Tasks

Create Database Tables

DDL
Create Staging Table

create database IF NOT EXISTS bible;

use bible;

DROP TABLE IF EXISTS bible.kjv_stage;

CREATE TABLE bible.kjv_stage
(

      id int               not null
                           AUTO_INCREMENT
                           
    , verse varchar(1000)  not null
    
    , primary key
      (
        id
      )
      
)
;

 

Create Actual Table

create database IF NOT EXISTS bible;

use bible;

DROP TABLE IF EXISTS bible.kjv;

CREATE TABLE bible.kjv
(

      bookID            smallint      not null
      
    , bookName          char(3)       not null
    
    , chapterNumber     smallint      not null
    
    , verseNumber       smallint      not null
    
    , verse             varchar(1000)  not null
    
    , primary key
      (
          bookID
        , chapterNumber
        , verseNumber
      )
      
)
;

 

Load Data

Outline

  1. Load Data
    • Load data into the staging table
    • Move data from the staging table into the actual table

Tasks

Load Data Into Staging Tables

SQL
SQL – Syntax

use bible;

truncate table [table-name];

LOAD DATA 

INFILE '[file-name]'

INTO TABLE [table-name]

LINES TERMINATED BY '[line-terminator]'

IGNORE [numberoflines] LINES

(
    [column-01], [column-02]....[column-n]
)
;


 

SQL – Sample

/*
    [1:1] In the beginning God created the heaven and the earth.
*/
use bible;

truncate table bible.kjv_stage;

LOAD DATA 

INFILE '/var/lib/mysql-files/bible.kjv.genesis.txt'

INTO TABLE bible.kjv_stage

LINES TERMINATED BY '\n'

IGNORE 1 LINES

(
    verse
)
;


 

Load Data Into Actual Tables

Outline
  1. Parse
    • Parse Text into individual data
      • Here is what the raw data looks like:
        • [1:1] In the beginning God created the heaven and the earth.
      • Parse out to:
        • Chapter Number => 1
        • Verse Number => 1
        • Verse => In the beginning God created the heaven and the earth.
SQL
SQL – Syntax

use bible;

truncate table [table-name-target];

insert into [table-name-target]

select *

from   [table-name-staging]
;


 

SQL – Sample

use bible;

truncate table bible.kjv;

insert into bible.kjv
(

      bookID            
      
    , bookName          
    
    , chapterNumber     
    
    , verseNumber       
    
    , verse             
         
)

select 
          1 as bookID            

        , 'GEN' as bookName          
            
        , trim(
                SUBSTRING(
                  tblB.verse
                , INSTR (tblB.verse, '[') + 1
                , INSTR (tblB.verse, ':') - ( INSTR (tblB.verse, '[') + 1 )
                )
            )
                as 'chapter'

        , trim(
                SUBSTRING(
                  tblB.verse
                , INSTR (tblB.verse, ':') + 1
                , INSTR (tblB.verse, ']') - ( INSTR (tblB.verse, ':') + 1 )
                )
            )
                as 'verse'

        , trim(
                SUBSTRING(
                  tblB.verse
                , INSTR (tblB.verse, ']') + 1
                , length(tblB.verse) - ( INSTR (tblB.verse, ']') + 1 )
                )
            )
                as 'verse'
                
from   bible.kjv_stage tblB

;

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s