How to load data from Alpha Vantage to Databricks Lakehouse

To access data from Alpha Vantage, you need an API key. Sign up on their website to receive your personal API key, which will allow you to make requests to their data services.

In your Databricks notebook, ensure you have the necessary Python libraries installed. This typically includes `requests` for making HTTP requests and `pandas` for handling data. You can install these by running `%pip install requests pandas` in a notebook cell.

Use the `requests` library to send an HTTP GET request to the Alpha Vantage API endpoint. Provide your API key and specify the desired data (e.g., stock time series). Retrieve the response in JSON format. Here's a basic example:
```python
import requests

api_key = 'your_alpha_vantage_api_key'
symbol = 'IBM'
function = 'TIME_SERIES_DAILY'
url = f'https://www.alphavantage.co/query?function={function}&symbol={symbol}&apikey={api_key}'

response = requests.get(url)
data = response.json()
```

Once you have the JSON response, convert it into a pandas DataFrame for easier manipulation and storage. Extract the relevant data fields and structure them appropriately:
```python
import pandas as pd

time_series = data['Time Series (Daily)']
df = pd.DataFrame.from_dict(time_series, orient='index')
df.index = pd.to_datetime(df.index)
df = df.sort_index()
```

Perform any necessary data cleaning and transformation within the pandas DataFrame. Rename columns, handle missing data, and ensure the data types are suitable for your analysis needs:
```python
df.columns = ['open', 'high', 'low', 'close', 'volume']
df = df.astype(float)
```

Convert the pandas DataFrame to a Spark DataFrame, then write it to a Delta table in your Databricks Lakehouse. This leverages Databricks' capabilities to efficiently store and manage large datasets:
```python
spark_df = spark.createDataFrame(df.reset_index())
spark_df.write.format('delta').mode('overwrite').save('/mnt/delta/alpha_vantage_data')
```

After saving the data, verify that it has been correctly stored by reading from the Delta table. This ensures that the data is accessible for future analysis:
```python
df_loaded = spark.read.format('delta').load('/mnt/delta/alpha_vantage_data')
display(df_loaded)
```

By following these steps, you can efficiently move data from Alpha Vantage to a Databricks Lakehouse without relying on third-party connectors or integrations.