> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wildflow.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Merge Data

> This tutorial shows how to **join** your **observational** data with **envirounmental** data

<Warning>
  This feature is in closed beta, please ask access via
  [WhatsApp](https://wildflow.ai/whatsapp) or
  [Discord](https://wildflow.ai/discord).
</Warning>

### Intro

* Observational data (e.g. 30MB of your shark trails) expected to have key columns `latitude`, `longitude` and `date` (and any other non-key columns).
* Envirounmental data (e.g. 100GB of [chlorophyll levels from Copernicus](https://data.marine.copernicus.eu/product/OCEANCOLOUR_GLO_BGC_L4_MY_009_104/description)) expected to have key columns `latitude` and `longitude` (and any other non-key columns).

The output table would have observational data (all the rows) and for each row we find nearest location (geo distance) from envirounmental data.

We use Google BigQuery as an underlying engine to perform this join.

### 1. Upload your observational data to **wildflow**

Make sure you uploaded your observational data file to [Wildflow](https://wildflow-demo.web.app/datasets). You can go to the `Datasets` tab and click on the `+` button and drag and drop your file there.

<Warning>
  For now it only supports `CSV` format. It only supports ISO date time (or
  anything Google BigQuery supports). E.g `2000-10-31 23:30`.
</Warning>

<img src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/upload-your-csv-file.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=bf1783cd138a6b7de006fe9f1cb7ef00" alt="upload-your-csv-file" width="2372" height="1234" data-path="images/tutorials/merge-data/upload-your-csv-file.png" />

Please specify the file name and make sure all the columns have correct names. File name should start with `raw.` for now (it's a name of the dataset/collection of tables in BigQuery).

<img src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/update-column-names.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=301bbc80e6de0da92b41dcaf0c6a4948" alt="update-column-names" width="1852" height="534" data-path="images/tutorials/merge-data/update-column-names.png" />

Proceed to upload your table.

### 2. Enable environmental data

If you need your dataset available in **wildflow**, please contact us via [WhatsApp](https://wildflow.ai/) or [Discord](https://wildflow.ai/discord).

Please provide us the name of the dataset, e.g.: [https://data.marine.copernicus.eu/product/OCEANCOLOUR\_GLO\_BGC\_L4\_MY\_009\_104](https://data.marine.copernicus.eu/product/OCEANCOLOUR_GLO_BGC_L4_MY_009_104)

### 3. Select tables to merge

Navigate to the [Workflows](https://wildflow-demo.web.app/workflows) tab and select tables you need to join:

* First choose your observational data (e.g. trails of your shark) and then choose large dataset (like chlorophyll levels).

* You can select as many environmental tables as you want.

<img src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/select-tables.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=10fe2a6c914a078dcae653b4890fd62f" alt="select-tables" width="1740" height="620" data-path="images/tutorials/merge-data/select-tables.png" />

### 3. Select result columns

<img src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/result-columns.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=2c70b7d01e0dcd4d6af8338c112ba7f7" alt="result-columns" width="1854" height="1214" data-path="images/tutorials/merge-data/result-columns.png" />

* Select only those columns you need.
* Make sure name of each column is unique.
* Specify name of the resulting table.

<Warning>
  - Merge will be happening based on the column `Type`
  - For now we don't support depth, but it should be easy to add.
</Warning>

### 4. SQL code

Now you should be able to see the SQL code generated to join these tables:

<img src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/code.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=ad4179d7c34ad0c24ec665def82ccaa4" alt="code" width="2048" height="1586" data-path="images/tutorials/merge-data/code.png" />

Feel free to save it for future experiment reproducibility.
You can run this in the Google BigQuery console.

### 5. Merge

When you continue the query will be executed:

<img width="300" src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/job-status.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=3620f553ae8560af07a19924fdf67eb0" data-path="images/tutorials/merge-data/job-status.png" />

You can see most recent jobs here: [Jobs](https://wildflow-demo.web.app/jobs).

And it should finish with a few minutes:

<img width="300" src="https://mintcdn.com/wildflow/4uNyYy0rsryKN1ga/images/tutorials/merge-data/job-complete.png?fit=max&auto=format&n=4uNyYy0rsryKN1ga&q=85&s=b83ba5d51605f6ae3eaeabc55a03629c" data-path="images/tutorials/merge-data/job-complete.png" />

Now you should be able to see a new table with merged observational and environmental data.

Yay! 🐳

### 6. Advanced Users

If you have a timestamp format that is not supported by BigQuery (`06/05/18 10:14` but you need `2018-05-06 10:14`), you can run this adhoc query to create `raw.test_table` from `gs://bucket-name/uploaded/Table_2024-04-10T16:59:21.145Z.csv`.

```sql theme={null}
load data overwrite raw.test_table (
  Timestamp string,
  Latitude float64,
  Longitude float64
) from files (
  format = 'CSV',
  uris = ['gs://bucket-name/uploaded/Table_2024-04-10T16:59:21.145Z.csv'],
  skip_leading_rows = 1
);

create or replace table raw.test_table as (
  select parse_timestamp('%d/%m/%y %H:%M', Timestamp) as Timestamp,
  Latitude,
  Longitude
  from raw.test_table
);

select * from raw.test_table limit 10
```