Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using JOIN query in AWS DynamoDB using PHP

I am currently using MySQL as database for my application in PHP. But now need to migrate to AWS DynamoDB. As I am new to DynamoDB, can anyone help me using JOIN in DynamoDB?

As per my finding, I have found that, JOINs can be used using Hive and Amazon EMR. But here also there is a problem that no resource is available for using Hive with PHP.

like image 822
Sanchit Avatar asked Jan 08 '16 12:01

Sanchit


2 Answers

hi maybe you can try this

To join two DynamoDB tables The join is computed on the cluster and returned. The join does not take place in DynamoDB. This example returns a list of customers and their purchases for customers that have placed more than two orders.

CREATE EXTERNAL TABLE hive_purchases(customerId bigint, total_cost double, items_purchased array<String>) 
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Purchases",
"dynamodb.column.mapping" = "customerId:CustomerId,total_cost:Cost,items_purchased:Items");

CREATE EXTERNAL TABLE hive_customers(customerId bigint, customerName string, customerAddress array<String>) 
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Customers",
"dynamodb.column.mapping" = "customerId:CustomerId,customerName:Name,customerAddress:Address");

Select c.customerId, c.customerName, count(*) as count from hive_customers c 
JOIN hive_purchases p ON c.customerId=p.customerId 
GROUP BY c.customerId, c.customerName HAVING count > 2;

To join two tables from different sources

In the following example, Customer_S3 is a Hive table that loads a CSV file stored in Amazon S3 and hive_purchases is a table that references data in DynamoDB. The following example joins together customer data stored as a CSV file in Amazon S3 with order data stored in DynamoDB to return a set of data that represents orders placed by customers who have "Miller" in their name.

CREATE EXTERNAL TABLE hive_purchases(customerId bigint, total_cost double, items_purchased array) STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' TBLPROPERTIES ("dynamodb.table.name" = "Purchases", "dynamodb.column.mapping" = "customerId:CustomerId,total_cost:Cost,items_purchased:Items");

CREATE EXTERNAL TABLE Customer_S3(customerId bigint, customerName string, customerAddress array<String>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LOCATION 's3://bucketname/path/subpath/';

Select c.customerId, c.customerName, c.customerAddress from 
Customer_S3 c 
JOIN hive_purchases p 
ON c.customerid=p.customerid 
where c.customerName like '%Miller%';

for more information you can read the documentation DynamoDB Export , Import Querys

good luck and try

like image 191
BlackHack123 Avatar answered Sep 30 '22 12:09

BlackHack123


well, migrating sql to NoSQL is a hard decision, you might want to take a look of this white page to see if your application can survive in the NoSQL world.

like image 25
Allen Avatar answered Sep 30 '22 11:09

Allen