A Comprehensive Guide to Extracting Data from MySQL Using Singer ETL
Tags: sql, opensource, etl, singer
Published: 2024-05-06 14:04:29

In this guide, we'll walk through the process of extracting data from MySQL using `tap-mysql` and loading it into `target-jsonl`. This seamless process ensures efficient data transfer while maintaining integrity.

## Step 1: Enable Python Virtual Environment (venv)

Even without administrative access, you can set up a Python virtual environment. Here's how:

- Create a new virtual environment:
    ```
    python3 -m venv <file_name>
    ```

- Navigate to the Script Directory within the created file and activate the virtual environment:
    ```
    .\activate.bat
    ```

Now, your virtual environment is active and ready for use.

## Step 2: Install `tap-mysql` and `target-jsonl`

Utilize pip to install the necessary packages:

```
pip install tap-mysql target-jsonl
```

## Step 3: Prepare Configuration Files

`tap-mysql` requires two input files: `config.json` and `properties.json`.

### `config.json`:
```json
{
    "host": "127.0.0.1",
    "port": "3306",
    "user": "root",
    "password": "root"
}
```

### `properties.json`:
```json
{
    "streams": [
        {
            "tap_stream_id": "sakila-actor_info",
            "table_name": "actor_info",
            "schema": {
                "properties": {
                    "actor_id": {
                        "inclusion": "available",
                        "minimum": 0,
                        "maximum": 65535,
                        "type": [
                            "null",
                            "integer"
                        ]
                    },
                    "first_name": {
                        "inclusion": "available",
                        "maxLength": 45,
                        "type": [
                            "null",
                            "string"
                        ]
                    },
                    "last_name": {
                        "inclusion": "available",
                        "maxLength": 45,
                        "type": [
                            "null",
                            "string"
                        ]
                    },
                    "film_info": {
                        "inclusion": "available",
                        "maxLength": 65535,
                        "type": [
                            "null",
                            "string"
                        ]
                    }
                },
                "type": "object"
            },
            "stream": "actor_info",
            "metadata": [
                {
                    "breadcrumb": [],
                    "metadata": {
                        "selected": true,
                        "replication-method": "FULL_TABLE",
                        "selected-by-default": false,
                        "database-name": "sakila",
                        "is-view": true
                    }
                },
                {
                    "breadcrumb": [
                        "properties",
                        "actor_id"
                    ],
                    "metadata": {
                        "selected-by-default": true,
                        "sql-datatype": "smallint unsigned"
                    }
                },
                {
                    "breadcrumb": [
                        "properties",
                        "first_name"
                    ],
                    "metadata": {
                        "selected-by-default": true,
                        "sql-datatype": "varchar(45)"
                    }
                },
                {
                    "breadcrumb": [
                        "properties",
                        "last_name"
                    ],
                    "metadata": {
                        "selected-by-default": true,
                        "sql-datatype": "varchar(45)"
                    }
                },
                {
                    "breadcrumb": [
                        "properties",
                        "film_info"
                    ],
                    "metadata": {
                        "selected-by-default": true,
                        "sql-datatype": "text"
                    }
                }
            ]
        }
    ]
}
```

## Step 4: Generate `properties.json`

Execute the following command in discover mode to generate `catalog.json`:
```
tap-mysql --config config.json --discover > catalog.json
```

Locate the JSON content of the desired table in `catalog.json` and copy it into another file. Let's name this file `selected_table.json`.

In `selected_table.json`, add the following lines within the curly braces to ensure the table is selected:
```json
{
    "streams": [ // Paste the content here ]
}
```

This step ensures that only the selected table is included for extraction.

## Step 5: Run `tap-mysql`

Execute the following command:
```
tap-mysql --config config.json --catalog selected_table.json
```

Congratulations! You've successfully extracted data from MySQL using `tap-mysql`.

## Step 6: Send Data to `jsonl` Target

Run the following command to send the data to `jsonl` target:
```
tap-mysql --config config.json --catalog selected_table.json | target-jsonl
```

A file with the same name as the table will be created.

## Step 7: Convert Output to a DataFrame

Here's an example of how to convert the output to a DataFrame using Python:

```python
import pandas as pd
import json

data = []
with open("<file_name>.jsonl", "r") as f:
    for line in f:
        data.append(json.loads(line))

df = pd.DataFrame(data)
print(df.columns)
```

This step allows for further analysis and manipulation of the extracted data.

*By following these steps, you've successfully extracted and transformed data from MySQL into a structured format, ready for analysis and insights.*

---