Import and Export Data

name: portada
layout: true
class: portada-slide, middle, right
---
# Import and Export Data
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Import and Export

Since this moment, we have seen how to insert data into the database using the `INSERT` statement or
how to retrieve it using the `SELECT` statement from the SQL language.

But what if we want to share this data with another person or load a file with data to the database?

Using a SQL script with INSERTS might not be the best idea, but a different data format can be used such as CSV, XML, JSON, etc.

This allows exporting and importing data in a more universal and versatile way, that other programs can interpret too.

---
# DBMS and SQL
Remember that __Databases Managment Systems__ follow a __server/client__ structure and __SQL__ is a language that
allows a client to communicate with a DBMS server.

![dbms](img/sql_dbms.png)

This has to be taken into account in order to understand the different methods of exporting and importing data.

---
# Importing data
```
LOAD DATA [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
```

.center[[MariaDB Importing Data](https://mariadb.com/kb/en/importing-data-into-mariadb/)]
.center[[MariaDB LOAD DATA INFILE](https://mariadb.com/kb/en/load-data-infile/)]

---
# Importing data
- `[REPLACE | IGNORE]`: It specifies the behaviour if you try to import a element that its primary key is
  already present in the database.
- `{FIELDS | COLUMNS}`: Specifies how each field has to be read:
    - `TERMINATED BY 'string'`: Specifies how each column is delimited.
        - `Pere,Martí,34` → `TERMINATED BY ','`
    - `[[OPTIONALLY] ENCLOSED BY 'char']`: Specifies the character that encloses each column.
        - `"Pere","Martí","34"` → `ENCLOSED BY '"'`
- `LINES`: Specifies how each record is read:
    - `STARTING BY 'string'`: Specifies how row starts.
        - `Person: Pere,Martí,34` → `STARTING BY 'Person: '`
    - `TERMINATED BY 'string'`: Specifies how row ends. Usually `\n`.
- `[IGNORE number LINES]`: Ignore the first `number` lines.

---
# Importing data: Example

```text
DNI,name,surname
"57859988S","Batiste","Mas"
"55975608W","Albert","Buigues"
"26628135T","Pau","Martínez"
```

```
LOAD DATA INFILE 'data.csv'
    REPLACE INTO TABLE persons
    COLUMNS TERMINATED BY ','
            ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (DNI,name,surname)
```
---
# Importing data: LOCAL
```
LOAD DATA [LOCAL] INFILE 'file_name'
...
```

When you execute the `LOAD DATA INFILE` statement, MariaDB Server attempts to read the input file from
its own file system.

In contrast, when you execute the `LOAD DATA LOCAL INFILE` statement, the client
attempts to read the input file from its file system, and it sends the contents of the input file to the MariaDB Server.

This allows you to load files from the client's local file system into the database.

---
# Importing data: XML
MariaDB also allows to import data in XML format.

```
LOAD XML [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE [db_name.]tbl_name
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<tagname>']
    [IGNORE number {LINES | ROWS}]
    [(column_or_user_var,...)]
    [SET col_name = expr,...]
```

.center[[MariaDB LOAD XML](https://mariadb.com/kb/en/load-xml/)]

---
# Exporing data: Server side

```
SELECT ... INTO OUTFILE 'file_name'
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
```

The `INTO OUTFILE` option in a `SELECT` stament allows to save the data obtained by the statement into
a file, with the format specified in the next options. Those options behave the same as the `LOAD DATA INFILE`
previously seen.

The file is saved in the MariaDB Server. If a relative path is specified, the DB foldar will be used as a reference (usually `/var/lib/mysql`)

.center[[MariaDB OUTFILE](https://mariadb.com/kb/en/select-into-outfile/)]

---
# Exporing data: Client side
Exporting data from the client side depends on the client software you are using to connect to the database.

The most common and "easy" way to connect to a database using the terminal command `mysql`.

The manual with the full list of options can be read with the command: `man mysql`.

## Connect to the database
```bash
mysql -u <user> -p[password] -h <host>
```
- `-u <user>`: Database user.
- `-p [password]`: Use password to connect to the database. `[password]` can be ommited and will be asked on the terminal (more secure).
- `-h <host>`: Hostname (or IP addresss) of the database server.

```bash
mysql -u root -p1234 -h 192.168.56.101
```

---
# Exporing data: Client side
`mysql` allows executing a query directly using the `-e` option.

```bash
mysql -u <user> -p[password] -h <host> -e "SQL query"

mysql -u root -p1234 -h 192.168.56.101 -e "SELECT DNI,name,person FROM database.person;"
```

The previous command will output the query result as standard output in the terminal.

The output format can be changed with the following options:
- `--table, -t`: Display output in table format.
- `--batch, -B`: Print results using tab as the column separator, with each row on a new line.
- `--skip-column-names, -N`:  Do not write column names in results.
- `--xml, -X`: Produce XML output.

---
# Exporing data: Client side CSV
`mysql` doesn't provide additional formating options.

In order to generate a CSV (Comma-Separated File) file, we can "transform" a
tab-separated output by modifying the standard output using bash utilities.

```bash
mysql -u <user> -p[password] -h <host> -B -e "SQL query" | tr "\t" ","
```

- `tr` command: Translates a charset into another charset. In the previous command, all tabs are replaced by commas.

More information about the `tr` command: `man tr`

## Saving into a file
In order to save the standard output of any command, we can use the `>` operand.

```bash
mysql -u <user> -p[password] -h <host> -e "SQL query" -X > result.xml
```
---
# Summary
- __Import__:
    - __Server side__:
```
LOAD DATA INFLIE 'file_path' ...
```
    - __Client side__:
```
LOAD DATA LOCAL INFLIE 'file_path' ...
```
- __Export__:
    - __Server side__:
```
SELECT ... INTO OUTFILE 'file_path'
```
    - __Client side__:
```bash
mysql -e "SQL query" > file_path
```