Assume that the following foreign table has been created on JSON data in Amazon S3 bucket:
CREATE MULTISET FOREIGN TABLE riverflow ,FALLBACK ,
EXTERNAL SECURITY DEFINER TRUSTED AUTH_OBJECT ,
MAP = TD_MAP1
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE)
USING
(
LOCATION ('/s3/s3.amazonaws.com/td-usgs/DATA/')
PATHPATTERN ('$data/$siteno/$year/$month/$day')
);
- Create a dataframe on foreign table.
# Create dataframe on foreign table which contains data in JSON format on S3 >>> riverflow = DataFrame.from_table("riverflow") - Display the columns in the DataFrame.
# As seen from create table statement, table has two columns 'Location' and 'Payload' >>> riverflow.columns
['Location', 'Payload']
- Check the types of the columns.
# Let's check their types >>> riverflow.dtypes
Location str Payload str
- Print the content of the table.
# Let's print the DataFrame. # Equivalent SQL: # SELECT * FROM riverflow >>> riverflow.head().to_pandas()
Location Payload 0 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 1 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 2 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 3 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 4 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 5 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 6 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 7 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 8 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27... 9 /S3/s3.amazonaws.com/td-usgs/DATA/09380000/201... { "site_no":"09380000", "datetime":"2018-06-27...