import os
import pandas as pd
import numpy as np
from IPython.display import Image
import jaydebeapi
import urllib.parse
import sys
import matplotlib.pyplot as plt
import seaborn as sns
import time
from tabulate import tabulate
About the Dataset¶
The Global Commodity Trade Statistics can be downloaded from https://www.kaggle.com/datasets/unitednations/global-commodity-trade-statistics. To download the dataset, click on the 'Data Card' tab and scroll all the way below to access the '.csv' file. Use the download icon (highlighted below) to download the data file.
##Specify the path to your image file
image_path = 'Dataset.png'
width = 500
## Display the image
Image(filename=image_path, width=width)
The resulting zip file has the name commodity_trade_statistics_data.csv (as shown in the image below).
##Specify the path to your image file
image_path = 'file_save.png'
width = 500
## Display the image
Image(filename=image_path, width=width)
Right-click this file and click 'Extract All' to get the csv file which has the data.
##Specify the path to your image file
image_path = 'file_extract_all.png'
width = 400
## Display the image
Image(filename=image_path, width=width)
Loading the data into a dataframe & Data Cleaning¶
##Loading the data into a dataframe in Python
commodity_data_df = pd.read_csv('C:\\Users\\mg_su\\Desktop\\Work\\Work_Upwork\\Israel Imru_GridDB\\Python_GridDB_POC11\\commodity_trade_statistics_data.csv\commodity_trade_statistics_data.csv',sep=',') #Load the dataframe
C:\Users\mg_su\AppData\Local\Temp/ipykernel_23308/908296312.py:2: DtypeWarning: Columns (2) have mixed types. Specify dtype option on import or set low_memory=False.
commodity_data_df = pd.read_csv('C:\\Users\\mg_su\\Desktop\\Work\\Work_Upwork\\Israel Imru_GridDB\\Python_GridDB_POC11\\commodity_trade_statistics_data.csv\commodity_trade_statistics_data.csv',sep=',') #Load the dataframe
In order to do some insightful mining, we subset the dataframe to specific categories like dairy products and sugar. We also consider only the years 2014 to 2016 for this analysis.
# Assuming 'commodity_data_df' is your DataFrame
categories_to_keep = [
'04_dairy_products_eggs_honey_edible_animal_product_nes',
'17_sugars_and_sugar_confectionery'
]
# Use boolean indexing to subset the DataFrame
category_subset_df = commodity_data_df[commodity_data_df['category'].isin(categories_to_keep)]
Final_subset_df = (category_subset_df[category_subset_df['year']>=2014])
# Create a list of keywords to match
commodity_keywords = ['Milk', 'Cheese', 'Butter','Yogurt' ,'Buttermilk','Sugar']
# Create a regular expression pattern to match any of the keywords
pattern = '|'.join(commodity_keywords)
# Subset the DataFrame using str.contains() with the pattern
Final_subset_df = Final_subset_df[Final_subset_df['commodity'].str.contains(pattern, case=False)]
Final_subset_df_copy = Final_subset_df.copy()
# Replace NaN and inf with a specific value, e.g., 0
Final_subset_df_copy.loc[:, 'weight_kg'] = Final_subset_df_copy['weight_kg'].fillna(0).astype(float)
Final_subset_df_copy.loc[:, 'quantity'] = Final_subset_df_copy['quantity'].fillna(0).astype(float)
Final_subset_df_copy.loc[:, 'year'] = Final_subset_df_copy['year'].fillna(0).astype(str)
Final_subset_df_copy.loc[:, 'comm_code'] = Final_subset_df_copy['comm_code'].fillna(0).astype(str)
'The final subset has '+ str(len(Final_subset_df_copy))+ ' rows and ' + str(len(Final_subset_df_copy.columns)) + ' columns.'
'The final subset has 15484 rows and 10 columns.'
Containers in GridDB¶
GridDB supports two types of containers namely Collections and TimeSeries containers. In the context of NoSQL databases, a collection is a container for storing related data items, and each item in the collection is represented as a row with attributes (columns). GridDB collections are akin to SQL tables, with rows representing records and columns representing attributes, much like the structure found in traditional SQL databases.
Despite the similarities, there are some key differences between collections in GridDB and tables in relational databases.
Additionally, NoSQL databases typically offer horizontal scalability and can handle large volumes of data with high availability and performance, making them well-suited for certain use cases, such as time-series data, IoT applications, and big data analytics.
In summary, while collections in GridDB share some similarities with SQL tables in traditional relational databases, they also have distinct characteristics that make them suitable for specific data storage and processing needs in modern application scenarios.
Collection containers store data as key-value pairs. You can use Collection containers when you don't need to organize data based on time and when real-time data processing is not a primary concern.
TimeSeries containers, on the other hand, are optimized for storing data where each entry in the container has a timestamp associated with it. TimeSeries containers are suitable for scenarios where data is continuously generated with a timestamp, such as sensor data, IoT devices, log files, etc. The data in TimeSeries containers is organized based on time, and it provides efficient query capabilities for time-based data retrieval and analysis. TimeSeries containers can break down data in terms of hours, minutes, seconds, etc., depending on the level of granularity needed. If you are working with non-time series data, collections are the ideal choice. In this case, we will be creating a Collection.
A short note on the JayDeBeAPI¶
The JayDeBeApi is a Python module that enables connecting to databases that support the Java Database Connectivity (JDBC) standard. It acts as a bridge between Python and the database using JDBC, allowing users to interact with standalone and cloud databases. In this article, we use the GridDB Cloud environment which is currently only available in Japan but it will be available globally in the near-future. Before getting started, make sure that you whitelist your IP Address from within the GridDB portal. Refer to the GridDB cloud documentation for more information.
The JayDeBeApi library requires the JDBC driver specific to the database you want to connect to. In the case of GridDB, you need to have the GridDB JDBC driver (gridstore-jdbc.jar) installed.
Here are the steps to do so -
Download the GridDB JDBC drivers from the GridDB Cloud portal by accessing the 'Supports' page on the Cloud portal and scrolling down to the "GridDB Cloud Library and Plugin download" section. Note that the file downloaded is a zip archive. On extracting it, the JDBC driver files can be found within the JDBC folder.
-
The GridDB JDBC driver files are -
- gridstore-5.2.0.jar
- gridstore-jdbc-5.2.0.jar
- gridstore-advanced-5.2.0.jar
Save the jar files in the same folder location and in a location accessible by your Python environment. Add the location of the jar files to the CLASSPATH system environment variable. To do this on a Windows machine, access the 'Environment Variables' from the 'Control Panel'. Under the 'System Variables', create a new variable called 'CLASSPATH' and mention the locations of the 3 jar files. Install the JayDeBeApi Python package in your Python environment. You are now ready to connect to the GridDB database from Python.Once the connection is established, you can execute SQL queries and perform database operations using the JayDeBeApi connection object. It is important to note that the drivers for GridDB Cloud are different from GridDB OSS. If you get an error at any point, here is a list of error codes to get more context around the error - GridDB Error Codes. Ensure that the SSL Mode is set to 'sslMode=PREFERRED'. Ensure that the connectionRoute is set to PUBLIC. You can also refer to this GridDB article for more information.
We will use the GridDB notification provider method to connect to GridDB. First, make a note of the notificationProvider, the GridDB cluster name, the GridDB database name and the username/password to access the database. These values will be used to construct the connection string and establish the connection to GridDB.
- The Python application uses the jaydebeapi library to interact with Java Database Connectivity (JDBC) APIs.
- JayDeBeApi establishes a connection to the JDBC driver.
- The JDBC driver connects to the GridDB database.
##Specify the path to your image file
image_path = 'jaydebeapi.png'
width = 250
## Display the image
Image(filename=image_path, width=width)
Connecting to GriDB Cloud using JayDeBe API¶
notification_provider = "https://dbaasshareextconsta.blob.core.windows.net/dbaas-share-extcon-blob/trial1602.json?sv=2015-04-05&sr=b&st=2023-03-14T00%3A00%3A00.0000000Z&se=2073-03-14T00%3A00%3A00.0000000Z&sp=r&sig=h2VJ0xAqsnRsqWV5CAS66RifPIZ1PDCJ0x%2FiXb2FOhA%3D"
np_encode = urllib.parse.quote(notification_provider)
cluster_name = "XX" ## Specify the cluster name here
cn_encode = urllib.parse.quote(cluster_name)
database_name = "XX" ## Specify the database name here
dbn_encode = urllib.parse.quote(database_name)
sslMode = "&sslMode=PREFERRED" #sslMode should be PREFERRED and connectionRoute should be PUBLIC
sm_encode = urllib.parse.quote(sslMode)
username = 'XX'
password = 'XX'
#Construct the JDBC URL to be used to connect to the GridDB database. The format is -
# jdbc:gs:///clusterName/databaseName?notificationProvider=theNotificationProviderURL&sslMode=PREFERRED
url = "jdbc:gs:///" + cn_encode + "/" + dbn_encode + "?notificationProvider=" + np_encode + sm_encode
#print("JDBC URL is " + url)
conn = jaydebeapi.connect("com.toshiba.mwcloud.gs.sql.Driver",
url,
{'user': username, 'password': password,
'connectionRoute':'PUBLIC',
'loginTimeout': '20'}
, "gridstore-jdbc-5.2.0.jar") #ensure to provide the correct location of the gridstore-jdbc JAR library
print('success!')
success!
Advantages of using the JayDeBeAPI over the WebAPI¶
The GridDB JayDeBeAPI has several advantages over the WebAPI, especially when it comes to ease of use and flexibility. Here are some advantages of using the GridDB JayDeBeAPI:
Simplified Integration & Native Database Connectivity: The JayDeBeAPI allows users to interact with GridDB directly from Python, making it easier to integrate GridDB functionality into Python applications. Users don't need to handle HTTP requests or deal with JSON payloads, which greatly simplify the development process.
Performance: By using JayDeBeAPI, users can achieve better performance compared to the web API, as it eliminates the overhead of HTTP requests and JSON serialization/deserialization. This is particularly important when dealing with large volumes of data.
Ease of Container Creation: Creating containers using Jaydebeapi is straightforward. Users can define container schemas directly in their Python code, and the Jaydebeapi takes care of creating the containers in GridDB. Moreover, with the JayDeBeAPI, containers can be created using a simple 'CREATE TABLE' syntax similar to relational databases; unlike the WebAPI where the container creation needs to be passed as a JSON request.
Creating Containers in GridDB¶
Datatypes in GridDB¶
It is very important to determine the correct datatypes for columns, especially for the numeric columns. This will prevent arithmetic overflow and type conversion errors. To determine the right datatype, we need to look at the maximum and minimum of each column and compare it with GridDB datatype limits. Refer to this GridDB resource for more details. Below is a function that gives the minimum and maximum limits for each numerical datatype in GridDB.
def print_limits(data_type, min_value, max_value):
print(f"{data_type}: From: {min_value} To: {max_value}")
print_limits('BYTE', -2**7, 2**7 - 1)
print_limits('SHORT', -2**15, 2**15 - 1)
print_limits('INTEGER', -2**31, 2**31 - 1)
print_limits('LONG', -2**63, 2**63 - 1)
BYTE: From: -128 To: 127 SHORT: From: -32768 To: 32767 INTEGER: From: -2147483648 To: 2147483647 LONG: From: -9223372036854775808 To: 9223372036854775807
We look at the max values of the numerical columns to determine where they fit in.
print('The max value of weight_kg is ' + str(max(Final_subset_df['weight_kg'])))
print('The max value of quantity is ' + str(max(Final_subset_df['quantity'])))
print('The max value of trade_usd is ' + str(max(Final_subset_df['trade_usd'])))
print('The min value of weight_kg is ' + str(min(Final_subset_df['weight_kg'])))
print('The min value of quantity is ' + str(min(Final_subset_df['quantity'])))
print('The min value of trade_usd is ' + str(min(Final_subset_df['trade_usd'])))
The max value of weight_kg is 23660974748.0 The max value of quantity is 23660974748.0 The max value of trade_usd is 8282160986 The min value of weight_kg is 0.0 The min value of quantity is 0.0 The min value of trade_usd is 1
The optimal datatype for trade_usd is Long, quantity and weight_kg is Double.
sql_query1 = (f"""
CREATE TABLE commodity_trade_stats
(
country_or_area VARCHAR(100),
year VARCHAR(4),
comm_code VARCHAR(6),
commodity VARCHAR(100),
flow VARCHAR(15),
trade_usd LONG,
weight_kg DOUBLE,
quantity_name VARCHAR(25),
quantity DOUBLE,
category VARCHAR(100)
)
""")
with conn.cursor() as cursor:
cursor.execute(sql_query1)
Row Registration in GridDB¶
Here, we use the 'INSERT SQL' command in GridDB to insert rows into GridDB while iterating through each row of the pandas dataframe 'Commodity_data'. Each row is taken in as a tuple and loaded into the container 'commodity_trade_stats' in GridDB Cloud.
# Prepare the SQL statement for insertion
sql_query2 = "INSERT INTO commodity_trade_stats (country_or_area,year,comm_code,commodity,flow,trade_usd,weight_kg,quantity_name,quantity,category) VALUES (?,?,?,?,?,?,?,?,?,?)"
# Create a list of tuples containing the data to be inserted
data_to_insert = [tuple(row) for _, row in Final_subset_df_copy.iterrows()]
# Use a loop to insert each row of data and record the time taken
cursor = conn.cursor()
try:
start_time = time.time() # Record the start time
for row in data_to_insert:
cursor.execute(sql_query2, row)
end_time = time.time() # Record the end time
execution_time = end_time - start_time
print(f"Time taken for insertion: {execution_time:.6f} seconds")
except Exception as e:
# Handle any exceptions that may occur during execution
print("Error:", e)
# Commit the changes
conn.commit()
While the JaydebeAPI helps register rows into the GridDB container, we can check the status of the row registration from within GridDB Cloud. A simple select statement can be run using the 'Query' editor in GridDB cloud for this. This is especially useful when there are large number of rows to be registered into the container.
Moreover, when prepared statements are used for data ingestion in GridDB, it greatly decreases the time taken to load huge volumes of data. As an example, 15484 records with 10 columns required just around 10 minutes.
Querying data from GridDB¶
Though primarily a NoSQL Database primarily designed for time series and IoT (Internet of Things) Data; GridDB's collections container types can be used for a wide range of applications like User Profile Management, Inventory Management, e-commerce data storage, and for various data mining and analysis applications. In addition, GridDB has support for SQL-like querying through the JDBC client, JaydebeAPI. This makes transitioning to a NoSQL Database effortless for developers and data engineers who are accustomed to working primarily with Relational Databases. GridDB supports the SQL-92 standard which involves almost all features of the SQL language namely Joins, Subqueries, Scalar Functions, constraints, and so on. Requests made through the JaydebeAPI can encompass both DML (Data Manipulation Language) and DDL (Data Definition Language) statements. Refer to this resource to reference the DDL commands supported currently. Here are the DML clauses supported by GridDB.
Let's use GridDB's powerful and fast query engine to derive some insights.
Two styles of querying GridDB using JaydebeAPI¶
One style of querying involves running a 'Select statement' using the cursor.execute() command and storing the data in a dataframe. In this style, the column headers are manually added while converting to a dataframe. e.g. Below is an example for Style 1 -
Style 1
sql_query_style1 = "SELECT * FROM commodity_trade_stats WHERE lower(category) like '%dairy%'"
cursor = conn.cursor()
cursor.execute(sql_query_style1)
results = cursor.fetchall()
dairy_products_df = pd.DataFrame(results, columns=['country_area', 'year', 'comm_code', 'commodity', 'flow', 'trade_usd', 'weight_kg', 'quantity_name', 'quantity', 'category'])
cursor.close()
Style 2 involves using the description attribute of the cursor to retrieve the column names dynamically. The cursor.description contains information about each column returned by the query. We use a list comprehension to extract the column names and store them in the column_names list. After fetching the query results, the 'column_names' list can be used to identify the corresponding columns in each row of the result set. Below is an example for Style 2-
Style 2
sql_query_style2 = "SELECT * FROM commodity_trade_stats WHERE lower(category) like '%dairy%'"
cursor = conn.cursor()
cursor.execute(sql_query_style2)
column_names = [desc[0] for desc in cursor.description]
results = cursor.fetchall()
df = pd.DataFrame(results, columns=column_names)
cursor.close()
Which countries had exported the most dairy products between 2014 and 2016?¶
As the data has already been filtered to be between 2014 and 2016, we do not need the condition in the 'Where' clause.
# Convert data to Megatonnes
sql_query3 = '''SELECT country_or_area,sum(weight_kg)/1000000 as Total_Qty_MegaTons
FROM commodity_trade_stats
WHERE lower(category) like '%dairy%' and flow ='Export'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10'''
# Create a cursor to execute the query
cursor = conn.cursor()
# Execute the query
cursor.execute(sql_query3)
# Fetch the column names
column_names = [desc[0] for desc in cursor.description]
# Fetch the query results
results = cursor.fetchall()
# Convert the results to a Pandas DataFrame
Dairy_Products = pd.DataFrame(results, columns=column_names)
# Close the cursor and connection
cursor.close()
Dairy_Products = Dairy_Products.sort_values(by='Total_Qty_MegaTons', ascending=True)
# Plot for DC category
fig, ax = plt.subplots(1, 1, figsize=(12, 6))
bars_dairy = ax.barh(Dairy_Products['country_or_area'], Dairy_Products['Total_Qty_MegaTons'])
ax.set_xlabel('Total Quantity (Weight in Mega Tons (Mega Tonnes))')
ax.set_ylabel('Country')
ax.set_title('Top 10 Exporters of Dairy Produce - from 2014 to 2016')
for bar in bars_dairy:
width = bar.get_width()
ax.text(width, bar.get_y() + bar.get_height() / 2, int(width), ha='left', va='center')
Insight(s): Germany has exported the maximum amoung of dairy products followed by New Zealand, France, Netherlands and European Union-28 (EU-28). It's interesting to note that while most of the countries in the list are also part of the EU-28 states, these countries also export dair products separately.
Calculating the query times in GridDB¶
To demonstrate the speed of the query engine in GridDB, we use the 'time' module within Python. In the below example, we use nested subqueries and calculate the query retrieval speeds of GridDB.
What are the specific commodities being sold by the top 10 dairy exporters?¶
# Convert data to Megatonnes
sql_query4 = '''
SELECT
DISTINCT commodity
FROM
commodity_trade_stats
WHERE
country_or_area IN (
SELECT
DISTINCT country_or_area
FROM
(
SELECT
country_or_area,
sum(weight_kg)/ 1000000 as Total_Qty_MegaTons
FROM
commodity_trade_stats
WHERE
category = '04_dairy_products_eggs_honey_edible_animal_product_nes'
and flow = 'Export'
GROUP BY
1
ORDER BY
2 DESC
LIMIT
10
) top_exporters
)
AND category = '04_dairy_products_eggs_honey_edible_animal_product_nes'
'''
# Create a cursor to execute the query
cursor = conn.cursor()
# Record the start time
start_time = time.time()
# Execute the query
cursor.execute(sql_query4)
# Fetch the column names
column_names = [desc[0] for desc in cursor.description]
# Fetch the query results
results = cursor.fetchall()
# Record the end time
end_time = time.time()
# Calculate the time taken for the query to process
query_time = end_time - start_time
# Print the query time
print(f"Query took {query_time:.6f} seconds to process")
# Convert the results to a Pandas DataFrame
Top_Commodities = pd.DataFrame(results, columns=column_names)
# Close the cursor and connection
cursor.close()
Query took 0.417321 seconds to process
title = "Specific Commodities exported by Top Exporters of Dairy Products (2014 to 2016)"
table = tabulate(Top_Commodities, headers='keys', tablefmt='grid',showindex='Never')
print(title)
print(table)
Specific Commodities exported by Top Exporters of Dairy Products (2014 to 2016) +-------------------------------------------------------+ | commodity | +=======================================================+ | Milk and cream powder unsweetened < 1.5% fat | +-------------------------------------------------------+ | Cheese, blue-veined | +-------------------------------------------------------+ | Milk not concentrated nor sweetened < 1% fat | +-------------------------------------------------------+ | Milk and cream not concentrated nor sweetened < 6% fa | +-------------------------------------------------------+ | Milk and cream nes sweetened or concentrated | +-------------------------------------------------------+ | Milk not concentrated nor sweetened 1-6% fat | +-------------------------------------------------------+ | Buttermilk, curdled milk, cream, kephir, etc. | +-------------------------------------------------------+ | Cheese except fresh, grated, processed or blue-veined | +-------------------------------------------------------+ | Milk powder < 1.5% fat | +-------------------------------------------------------+ | Yogurt | +-------------------------------------------------------+ | Fresh cheese, unfermented whey cheese, curd | +-------------------------------------------------------+ | Natural milk products nes | +-------------------------------------------------------+ | Cheese, grated or powdered, of all kinds | +-------------------------------------------------------+ | Cheese processed, not grated or powdered | +-------------------------------------------------------+ | Milk and cream powder sweetened < 1.5% fat | +-------------------------------------------------------+ | Butter and other fats and oils derived from milk | +-------------------------------------------------------+ | Milk and cream unsweetened, concentrated | +-------------------------------------------------------+
Insight(s): Among the commodities exported by the Top 10 dairy exporters (Germany, New Zealand, etc.), we see that milk and cream with different fat percentages are being exported widely. Other commodities exported include cheese, butter, yogurt, natural milk products, milk and cream powder and derivatives from milk.
Calculating the Average query time¶
The fact that the above query took only a few seconds to process indicates that the query engine is performing relatively well for a container with 15848 records. However, the actual performance may vary depending on the complexity of the query and the hardware and configuration of the GridDB server.
To provide a more comprehensive performance analysis, we run the query multiple times and take the average time.
# Run the query multiple times to get the average execution time
num_iterations = 5
total_execution_time = 0
start_time = time.time()
# Create a cursor to execute the query
cursor = conn.cursor()
# Execute the query
cursor.execute(sql_query4)
# Fetch the results
query_results = cursor.fetchall()
# Close the cursor and connection
cursor.close()
end_time = time.time()
execution_time = end_time - start_time
total_execution_time += execution_time
# Calculate the average execution time
average_execution_time = total_execution_time / num_iterations
print(f"Average Query Execution Time: {average_execution_time:.6f} seconds")
Average Query Execution Time: 0.083651 seconds
We see that the average query execution time is just around 0.08 seconds. Being a Modern Cloud database, GridDB uses various caching techniques to improve query performance and reduce the need to access disk storage repeatedly. Caching helps store frequently accessed data in memory, which significantly speeds up the retrieval of that data during subsequent queries. GridDB employs an in-memory cache to store frequently accessed data from containers. GridDB also has a query cache that can cache the results of frequently executed queries. GridDB optimizes query execution by creating and maintaining execution plans. These execution plans help to minimize the resources required for query processing and enhance overall performance. GridDB uses a specialized cache to store frequently accessed time-series data efficiently. This improves data retrieval performance for time-series queries. In GridDB's clustering environment, multiple nodes can share cached data to further enhance data availability and reduce data access latency.
What are the Top 15 Sugar/Sugar Confectionary Exporters across the world?¶
# Convert data to Megatonnes
sql_query5 = '''SELECT country_or_area,sum(weight_kg)/1000000 as Total_Qty_MegaTons
FROM commodity_trade_stats
WHERE category like '%sugar%' and flow in ('Export','Re-Export')
GROUP BY 1
ORDER BY 2 DESC
LIMIT 15'''
# Create a cursor to execute the query
cursor = conn.cursor()
# Execute the query
cursor.execute(sql_query5)
# Fetch the column names
column_names = [desc[0] for desc in cursor.description]
# Fetch the query results
results = cursor.fetchall()
# Convert the results to a Pandas DataFrame
Sugar_Products = pd.DataFrame(results, columns=column_names)
# Close the cursor and connection
cursor.close()
Sugar_Products = Sugar_Products.sort_values(by='Total_Qty_MegaTons', ascending=True)
# Plot for DC category
fig, ax = plt.subplots(1, 1, figsize=(12, 6))
bars_dairy = ax.barh(Sugar_Products['country_or_area'], Sugar_Products['Total_Qty_MegaTons'])
ax.set_xlabel('Total Quantity (Weight in Mega Tons (Mega Tonnes))')
ax.set_ylabel('Country')
ax.set_title('Top 15 Exporters of Sugar/Confectionary - from 2014 to 2016')
for bar in bars_dairy:
width = bar.get_width()
ax.text(width, bar.get_y() + bar.get_height() / 2, int(width), ha='left', va='center')
Insight(s): Brazil, Thailand and India are the top 3 exporters of sugar and sugar-based confectionary. Let's see what types of sugar products get exported by these countries in the next query.
What are different types of sugar products being exported by the Top 3 sugar exporters?¶
GridDB supports Window Functions. Refer to this resource to learn more.
sql_query6 = '''
SELECT *
FROM
(
SELECT
DISTINCT country_or_area,commodity,max(trade_usd) as Highest_Trade_USD,
ROW_NUMBER() OVER(PARTITION BY country_or_area ORDER BY trade_usd desc) as rank
FROM
commodity_trade_stats
WHERE
country_or_area IN (
SELECT
DISTINCT country_or_area
FROM
(
SELECT
country_or_area,
sum(weight_kg)/ 1000000 as Total_Qty_MegaTons
FROM
commodity_trade_stats
WHERE
lower(category) like '%sugar%'
and flow = 'Export'
GROUP BY
1
ORDER BY
2 DESC
LIMIT
3
) top_exporters
)
AND lower(category) like '%sugar%'
GROUP BY 1,2
ORDER BY 1,2,3 desc
)data
WHERE rank <=3
order by country_or_area,rank asc
'''
# Create a cursor to execute the query
cursor = conn.cursor()
# Record the start time
start_time = time.time()
# Execute the query
cursor.execute(sql_query6)
# Fetch the column names
column_names = [desc[0] for desc in cursor.description]
# Fetch the query results
results = cursor.fetchall()
# Record the end time
end_time = time.time()
# Calculate the time taken for the query to process
query_time = end_time - start_time
# Print the query time
print(f"Query took {query_time:.6f} seconds to process")
# Convert the results to a Pandas DataFrame
Top_sugar_Commodities = pd.DataFrame(results, columns=column_names)
# Close the cursor and connection
cursor.close()
Query took 0.442810 seconds to process
title = "Most valued Sugar Commodities (in terms of trade_usd) exported by the Top 3 Exporters (2014 to 2016)"
table = tabulate(Top_sugar_Commodities, headers='keys', tablefmt='grid',showindex='Never')
print(title)
print(table)
Most valued Sugar Commodities (in terms of trade_usd) exported by the Top 3 Exporters (2014 to 2016) +-------------------+-------------------------------------------------------+---------------------+--------+ | country_or_area | commodity | Highest_Trade_USD | rank | +===================+=======================================================+=====================+========+ | Brazil | Raw sugar, cane | 8282160986 | 1 | +-------------------+-------------------------------------------------------+---------------------+--------+ | Brazil | Refined sugar, in solid form, nes, pure sucrose | 2153226293 | 2 | +-------------------+-------------------------------------------------------+---------------------+--------+ | Brazil | Chewing gum containing sugar, except medicinal | 28568364 | 3 | +-------------------+-------------------------------------------------------+---------------------+--------+ | India | Refined sugar, in solid form, nes, pure sucrose | 1322782674 | 1 | +-------------------+-------------------------------------------------------+---------------------+--------+ | India | Raw sugar, cane | 918725422 | 2 | +-------------------+-------------------------------------------------------+---------------------+--------+ | India | Sugar confectionery not chewing gum, no cocoa content | 92674431 | 3 | +-------------------+-------------------------------------------------------+---------------------+--------+ | Thailand | Raw sugar, cane | 1442905839 | 1 | +-------------------+-------------------------------------------------------+---------------------+--------+ | Thailand | Refined sugar, in solid form, flavoured or coloured | 28690034 | 2 | +-------------------+-------------------------------------------------------+---------------------+--------+ | Thailand | Sugar confectionery not chewing gum, no cocoa content | 134849053 | 3 | +-------------------+-------------------------------------------------------+---------------------+--------+
Insight(s): We see that raw cane sugar, refined sugar, chewing gums and sugar confectionary are some of the major sugar products being exported to other parts of the world. In terms of trade_USD, raw sugar from Brazil has the highest trade value with 8.28 billion USD. Thailand's raw cane sugar is at 1.44 billion USD followed by India's raw cane sugar at about 1.3 billion USD. While Brazil exports chewing gums with sugar, India and Thailand are big at selling sugar confectionary. All three countries also export refined sugar, of which Brazil's refined sugar is at the highest trade_usd value.
Which countries have experienced the highest growth in cheese imports?¶
GridDB supports mathematical and aggregate functions including Min-Max functions. Refer to this resource to learn more.
sql_query7 = '''
SELECT country_or_area,
sum(case when year = '2015' then -1*qty_megatonnes
when year = '2016' then qty_megatonnes
end
) as import_growth_megatonnes
FROM
(
SELECT country_or_area,
year,
(sum(quantity) / 1000000) AS qty_megatonnes
FROM commodity_trade_stats
WHERE lower(commodity) LIKE '%cheese%'
AND flow = 'Import'
and year in ('2015','2016')
GROUP BY 1,2
ORDER BY 3 DESC
)data
group by 1
order by 2 desc
LIMIT 5
'''
# Create a cursor to execute the query
cursor = conn.cursor()
# Record the start time
start_time = time.time()
# Execute the query
cursor.execute(sql_query7)
# Fetch the column names
column_names = [desc[0] for desc in cursor.description]
# Fetch the query results
results = cursor.fetchall()
# Record the end time
end_time = time.time()
# Calculate the time taken for the query to process
query_time = end_time - start_time
# Print the query time
print(f"Query took {query_time:.6f} seconds to process")
# Convert the results to a Pandas DataFrame
Import_Growth = pd.DataFrame(results, columns=column_names)
# Close the cursor and connection
cursor.close()
Query took 0.422426 seconds to process
title = "Countries with the highest growth of cheese imports (2015 and 2016)"
table = tabulate(Import_Growth, headers='keys', tablefmt='grid',showindex='Never')
print(title)
print(table)
Countries with the highest growth of cheese imports (2015 and 2016) +-------------------+----------------------------+ | country_or_area | import_growth_megatonnes | +===================+============================+ | Belgium | 36.1647 | +-------------------+----------------------------+ | Germany | 22.0728 | +-------------------+----------------------------+ | China | 21.5935 | +-------------------+----------------------------+ | Brazil | 21.5245 | +-------------------+----------------------------+ | Australia | 11.1461 | +-------------------+----------------------------+
Insight(s): Belgium, Germany, China, Brazil and Australia saw an increased growth in terms of cheese imports between 2015 and 2016.
Concluding Remarks¶
To sum it all up, we used GridDB to analyze and store large volumes of trade statistics and commodity data in the context of international trade. GridDB proved to be an excellent choice for this task, offering high performance, scalability, and efficient handling of data.
Using GridDB's SQL-like query language, we easily developed and executed simple to complex queries to extract valuable insights from the dataset. The ability to work with various data types, including numerical and string data further enhanced the flexibility of our analysis.
Moreover, GridDB's high availability and reliability features, such as data replication and automatic failover mechanisms, ensured the continuous availability of trade data, minimizing the risk of data loss and downtime.
The analysis provided valuable information for business intelligence, enabling us to identify top exporters, track trade trends, and explore commodity trade patterns among various countries. Overall, GridDB's capabilities proved instrumental in empowering us to gain actionable insights from the trade and commodity data, contributing to better-informed business decisions.
If you have any questions about the blog, please create a Stack Overflow post here https://stackoverflow.com/questions/ask?tags=griddb .
Make sure that you use the “griddb” tag so our engineers can quickly reply to your questions.
I like it