DURGESH99P/Databricks-delta-lake-house-architecture-for-Amazon-Reviews-datalake-stored-in-external-S3-bucket

%sql
CREATE SCHEMA IF NOT EXISTS amazon_reviews;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

%sql
DESCRIBE SCHEMA EXTENDED amazon_reviews;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

database_description_item	database_description_value
Catalog Name	hive_metastore
Namespace Name	amazon_reviews
Comment
Location	dbfs:/user/hive/warehouse/amazon_reviews.db
Owner	root
Properties

%sql
USE amazon_reviews;
CREATE OR REPLACE TEMPORARY VIEW temp_table USING csv
OPTIONS(PATH = 's3://reviews-amazon/Data_source/amazon_reviews.csv',
header = "true",
mode= "PERMISSIVE"
);

CREATE OR REPLACE TABLE reviews AS 
SELECT * FROM temp_table;

SELECT * FROM reviews LIMIT 10;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

id0	name	asins	brand	categories	keys	manufacturer	date	dateAdded	dateSeen	didPurchase	doRecommend	id12	numHelpful	rating	sourceURLs	text	title	userCity	userProvince	username
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-13T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=166	This product so far has not disappointed. My children love to use it and I like the ability to monitor control what content they see with ease.	Kindle	null	null	Adapter
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-13T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	great for beginner or experienced person. Bought as a gift and she loves it	very fast	null	null	truman
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-13T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	Inexpensive tablet for him to use and learn on, step up from the NABI. He was thrilled with it, learn how to Skype on it already...	Beginner tablet for our 9 year old son.	null	null	DaveZ
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-13T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	4	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	I've had my Fire HD 8 two weeks now and I love it. This tablet is a great value.We are Prime Members and that is where this tablet SHINES. I love being able to easily access all of the Prime content as well as movies you can download and watch laterThis has a 1280/800 screen which has some really nice look to it its nice and crisp and very bright infact it is brighter then the ipad pro costing $900 base model. The build on this fire is INSANELY AWESOME running at only 7.7mm thick and the smooth glossy feel on the back it is really amazing to hold its like the futuristic tab in ur hands.	Good!!!	null	null	Shacks
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-12T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	I bought this for my grand daughter when she comes over to visit. I set it up with her as the user, entered her age and name and now Amazon makes sure that she only accesses sites and content that are appropriate to her age. Simple to do and she loves the capabilities. I also bought and installed a 64gig SD card which gives this little tablet plenty of storage. For the price I think this tablet is best one out there. You can spend hundreds of dollars more for additional speed and capacity but when it comes to the basics this tablets does everything that most people will ever need at a fraction of the cost.	Fantastic Tablet for kids	null	null	explore42
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-12T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	This amazon fire 8 inch tablet is the perfect size. I purchased it for my husband so that he has a bigger screen than just his phone. He had gotten me one a few years ago so I knew it would be a good purchase.	Just what we expected	null	null	tklit
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-12T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	4	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	Great for e-reading on the go, nice and light weight, and for the price point given, definitely worth the purchase.	great e-reader tablet	null	null	Droi
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-12T00:00:00.000Z	2017-07-03T23:33:15Z	2017-06-07T09:04:00.000Z,2017-04-30T00:45:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=200,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=167	I gave this as a Christmas gift to my inlaws, husband and uncle. They loved it and how easy they are to use with fantastic features!	Great for gifts	null	null	Kacy
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-23T00:00:00.000Z	2017-07-03T23:28:24Z	2017-06-07T09:04:00.000Z,2017-04-30T00:44:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=154,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=120	Great as a device to read books. I like that it links with my borrowed library e-books. Switched from another popular tablet brand and I am happy with the choice I made. It took some time to get books from my previous non-Kindle reader, but finally figured out a way!	Great for reading	null	null	Weebee
AVqkIhwDv8e3D1O-lebb	All-New Fire HD 8 Tablet, 8 HD Display, Wi-Fi, 16 GB - Includes Special Offers, Magenta	B01AHB9CN2	Amazon	Electronics,iPad & Tablets,All Tablets,Fire Tablets,Tablets,Computers & Tablets	841667104676,amazon/53004484,amazon/b01ahb9cn2,0841667104676,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/5620406,allnewfirehd8tablet8hddisplaywifi16gbincludesspecialoffersmagenta/b01ahb9cn2	Amazon	2017-01-23T00:00:00.000Z	2017-07-03T23:28:24Z	2017-06-07T09:04:00.000Z,2017-04-30T00:44:00.000Z	null	TRUE	null	0	5	http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=154,http://reviews.bestbuy.com/3545/5620406/reviews.htm?format=embedded&page=121	I love ordering books and reading them with the reader.	Great and lightweight reader	null	null	RoboBob

%sql
describe detail reviews;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

format	id	name	description	location	createdAt	lastModified	partitionColumns	clusteringColumns	numFiles	sizeInBytes	properties	minReaderVersion	minWriterVersion	tableFeatures	statistics
delta	9961657c-5a68-4f95-8a9f-9f9a32e30f23	hive_metastore.amazon_reviews.reviews	null	dbfs:/user/hive/warehouse/amazon_reviews.db/reviews	2023-11-08T04:59:42.974Z	2023-11-08T04:59:57Z	List()	List()	4	4371123	Map()	2	5	List(appendOnly, changeDataFeed, checkConstraints, columnMapping, generatedColumns, invariants)	Map()

%sql
DESCRIBE reviews;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

col_name	data_type	comment
id0	string	null
name	string	null
asins	string	null
brand	string	null
categories	string	null
keys	string	null
manufacturer	string	null
date	string	null
dateAdded	string	null
dateSeen	string	null
didPurchase	string	null
doRecommend	string	null
id12	string	null
numHelpful	string	null
rating	string	null
sourceURLs	string	null
text	string	null
title	string	null
userCity	string	null
userProvince	string	null
username	string	null

%sql
SELECT count(*),count(id0),
count(name),
count(asins),
count(brand),
count(categories),
count(keys),
count(manufacturer),
count(date),
count(dateAdded),
count(dateSeen),
count(didPurchase),
count(doRecommend),
count(id12),
count(numHelpful),
count(rating),
count(sourceURLs),
count(text),
count(title),
count(userCity),
count(userProvince),
count(username) FROM reviews

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

count(1)	count(id0)	count(name)	count(asins)	count(brand)	count(categories)	count(keys)	count(manufacturer)	count(date)	count(dateAdded)	count(dateSeen)	count(didPurchase)	count(doRecommend)	count(id12)	count(numHelpful)	count(rating)	count(sourceURLs)	count(text)	count(title)	count(userCity)	count(userProvince)	count(username)
41421	41421	28046	28029	34645	34532	34532	34660	34628	30751	34644	960	34253	719	33795	28593	33944	34441	33952	7057	6941	28597

Creating Table which represents Gold data

%sql
create table reviews_gold as
select * from reviews;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

num_affected_rows	num_inserted_rows

Removing Non Relevant Columns

%sql
ALTER TABLE reviews_gold
DROP column userCity;

<style scoped> .ansiout { display: block; unicode-bidi: embed; white-space: pre-wrap; word-wrap: break-word; word-break: break-all; font-family: "Menlo", "Monaco", "Consolas", "Ubuntu Mono", "Source Code Pro", monospace; font-size: 13px; color: #555; margin-left: 4px; line-height: 19px; } </style>

com.databricks.backend.common.rpc.SparkDriverExceptions$SQLExecutionException: com.databricks.sql.transaction.tahoe.DeltaAnalysisException: DROP COLUMN is not supported for your Delta table. Please enable Column Mapping on your Delta table with mapping mode 'name'. You can use one of the following commands.

If your table is already on the required protocol version: ALTER TABLE table_name SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name')

If your table is not on the required protocol version and requires a protocol upgrade: ALTER TABLE table_name SET TBLPROPERTIES ( 'delta.columnMapping.mode' = 'name', 'delta.minReaderVersion' = '2', 'delta.minWriterVersion' = '5') Refer to table versioning at https://docs.databricks.com/delta/versioning.html

%sql
ALTER TABLE reviews_gold SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5');

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

%sql
ALTER TABLE reviews_gold
DROP COLUMN userCity;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

%sql
ALTER TABLE reviews_gold
DROP COLUMN userProvince;
ALTER TABLE reviews_gold
DROP COLUMN id12;
ALTER TABLE reviews_gold
DROP COLUMN didPurchase;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

%sql
describe reviews_gold

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

col_name	data_type	comment
id0	string	null
name	string	null
asins	string	null
brand	string	null
categories	string	null
keys	string	null
manufacturer	string	null
date	string	null
dateAdded	string	null
dateSeen	string	null
doRecommend	string	null
numHelpful	string	null
rating	string	null
sourceURLs	string	null
text	string	null
title	string	null
username	string	null

Removing Null Values

%sql
delete from reviews_gold where text is null;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

num_affected_rows
6980

%sql
select count(*) from reviews_gold

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

count(1)
34441

Deduplication

%sql
select count(*) from 
(select distinct * from reviews_gold) t;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

count(1)
32476

%sql
CREATE OR REPLACE TABLE reviews_gold as
SELECT DISTINCT * FROM reviews_gold;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

num_affected_rows	num_inserted_rows

%sql
SELECT count(*) FROM reviews_gold;

<style scoped> .table-result-container { max-height: 300px; overflow: auto; } table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 5px; } th { text-align: left; } </style>

count(1)
32476