How to load data from TrustPilot to Postgres destination

To extract data from Trustpilot, you need to access their API. First, ensure you have a Trustpilot account and then obtain the necessary API credentials (API key and secret) from the Trustpilot Developer Portal. These credentials will allow you to authenticate requests to the API.

Review the Trustpilot API documentation to identify the endpoints that provide the data you need. For example, if you want to retrieve reviews, locate the endpoint that serves review data. Make note of any parameters you need to specify, such as business ID, filters, or pagination.

Use a programming language like Python to send HTTP GET requests to the Trustpilot API endpoints. You can use libraries like `requests` in Python to handle these requests. Ensure you include your API credentials in the request headers for authentication. Retrieve the data in JSON format.

```python
import requests

url = "https://api.trustpilot.com/v1/business-units/{businessUnitId}/reviews"
headers = {"Authorization": "Bearer YOUR_API_KEY"}
response = requests.get(url, headers=headers)
data = response.json()
```

Once you have retrieved the JSON data, parse it to extract the necessary information. Depending on your needs, you might need to transform the data into a format suitable for PostgreSQL. For instance, you can map JSON fields to PostgreSQL table columns and handle any data type conversions.

```python
reviews = data['reviews']
parsed_reviews = []
for review in reviews:
parsed_review = {
'review_id': review['id'],
'consumer_name': review['consumer']['name'],
'rating': review['stars'],
'text': review['text'],
'date': review['createdAt']
}
parsed_reviews.append(parsed_review)
```

Establish a connection to your PostgreSQL database using a library like `psycopg2` in Python. You will need the database host, port, database name, user, and password to connect.

```python
import psycopg2

conn = psycopg2.connect(
dbname="your_dbname",
user="your_username",
password="your_password",
host="localhost",
port="5432"
)
cursor = conn.cursor()
```

Create a table in your PostgreSQL database to store the data. Then, construct SQL `INSERT` statements to add the parsed data to your table. Use the cursor to execute these statements.

```python
create_table_query = """
CREATE TABLE IF NOT EXISTS reviews (
review_id VARCHAR PRIMARY KEY,
consumer_name VARCHAR,
rating INT,
text TEXT,
date TIMESTAMP
);
"""
cursor.execute(create_table_query)
conn.commit()

insert_query = """
INSERT INTO reviews (review_id, consumer_name, rating, text, date)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT (review_id) DO NOTHING;
"""
for review in parsed_reviews:
cursor.execute(insert_query, (review['review_id'], review['consumer_name'], review['rating'], review['text'], review['date']))
conn.commit()
```

After inserting the data, close the cursor and the connection to the PostgreSQL database. Verify that the data has been correctly inserted by querying the table. This ensures that the data migration process was successful.

```python
cursor.close()
conn.close()

# Verify data (optional)
# Execute a SQL SELECT query to review data
```

By following these steps, you can manually move data from Trustpilot to a PostgreSQL destination without relying on third-party connectors or integrations.