The following post will walk through the steps to connect Row Zero to AWS S3 to import data from Amazon's cloud data storage into a spreadsheet for easy analysis. Continue reading for more information or use the table of contents to skip directly to specific sections.
Contents
- How to analyze S3 data in a spreadsheet
- What is AWS S3?
- The benefits of storing data in S3
- The challenges of analyzing data in S3
- Conclusion
How to analyze S3 data in a spreadsheet
There are 3 steps to easily analyze S3 data in a spreadsheet. Follow the instructions below to get started.
- Connect a S3 bucket to a Row Zero spreadsheet
- Browse the S3 bucket and import a file
- Open and combine multiple S3 files in a spreadsheet
Browse a S3 bucket and import a file
Once you've successfully connected your S3 bucket to Row Zero with the instructions below, you can browse S3 and select a file to open. Simply click on Data > Import from Amazon S3. Then select your file and it will import into the spreadsheet. 
Open and combine multiple S3 files in a spreadsheet
For users with multiple related files in one folder, Row Zero can combine, merge, and concatenate all the files into one dataset, making the data easier to analyze in a spreadsheet. One your S3 bucket is connected, navigate to any folder in S3 via the Data > Import from Amazon S3 menu and select the files you would like to combine. Then use the import windo to verify whether your data has headers and hit 'import.' Row Zero will concatenate the files into one worksheet. 
Connect to a S3 bucket to Row Zero
The following instructions explain how to create an IAM role in AWS S3 and grant permissions for a Row Zero workbook to connect a specific S3 bucket and ingest data. To begin the process, in the Data menu, select Import from Amazon S3. 
You will see a link to the public datasets S3 bucket and a button in the upper right hand corner that says + connect bucket. Click the button to connect a new bucket. The public datasets bucket is available to demo the integration and Row Zero features. 
The first step is identifying the bucket name you intend to connect to Row Zero and entering the name in the provided text box. 
Next, provide the Amazon Resource Name (ARN). To identify the name, follow the instructions below to create an IAM role for Row Zero and grab the ARN from the AWS console. At the top of the screen your Row Zero AWS Account ID and your External ID will be visible. Both will be needed to create the IAM role. 
Create an IAM Role
Log in to the AWS Management Console
Select AWS Account
Select Another AWS Account and enter 732940336628 as the account ID
Select Require External Id and enter Your Account ID
Click Next
Select Create Policy (this will open a new tab)
Select the tab that says JSON and replace the text with this:
Click Next: Tags
Click Next: Review
Give the policy a name like 'RowZero-myS3bucket-ReadOnly'
Click Create policy and close this browser tab
On the first browser tab, refresh the list of policies
Search for the policy you just created (e.g. RowZero-myS3bucket-ReadOnly), click the checkbox to its left, and click Next
Give the role a name, like RowZero-myS3bucket-ReadOnly
Scroll to the bottom of the page, and click Create Role
Click View Role at the top of the screen and copy the role ARN and paste it in the text box at the top of the window. Click Next.
What is AWS S3?
Amazon S3 (Simple Storage Service) is a cloud-based object storage service provided by Amazon Web Services (AWS). It enables individuals and organizations to store and retrieve data, images, videos, and other files on the internet.
AWS S3 is designed to be highly scalable, durable, and secure. The service allows users to store unlimited amounts of data in virtually any format, with high availability and reliability.
Some common use cases for Amazon S3 include backup and disaster recovery, data archiving, content storage, distribution, and web hosting. Many businesses also use Amazon S3 as a data lake for big data analytics and machine learning.
The benefits of storing data in S3
Storing datasets in Amazon S3 offers unparalleled durability, scalability, and security for data storage needs, making it an ideal solution for businesses of all sizes. Its scalability allows users to store and retrieve any amount of data at any time, from anywhere on the web, providing flexibility to support both small and large-scale operations. Additionally, S3's robust security features, including built-in encryption and access management tools, ensure that data is protected and only accessible to authorized users, making it a trusted choice for storing sensitive information.
The challenges of analyzing data in S3
While S3 makes it incredibly easy to store large amounts of data, it can be challenging to analyze data stored in S3 for 3 reasons:
Dataset Size
Processing large datasets is a challenge for spreadsheets, like Google Sheets and Microsoft Excel, and requires a more powerful spreadsheet. Additionally, exporting data from S3 to a local machine and then uploading to a hosted application is a slow and tedious process when datasets are large. It is much easier to connect directly to a S3 bucket and browse for files that open in your analytics tool of choice.
Access Control
S3 provides various access control mechanisms, such as IAM policies and bucket policies, which can restrict or limit access to data stored in S3. This can make it challenging to ensure that users have the necessary permissions to access and analyze the data they need.
Data Integration
Data stored in S3 may need to be integrated with other data sources before analysis. This can require additional processing and transformation steps to ensure data consistency and accuracy.
Conclusion
AWS S3 is an affordable and efficient data storage service that can house extremely large data sets. When analyzing data stored in S3, it can be challenging to work with large file sizes. Often the process involves downloading data sets to local computers and then opening them with software on the computer or uploading to other cloud services. It is most efficient to load data from S3 directly into a cloud storage service, like Row Zero. Row Zero connects directly to S3 importing data in seconds. Row Zero is a spreadsheet designed for big data that can open billion row data sets making it a great solution for analyzing data in AWS S3.
Aside from S3, Row Zero also connects to the major data warehouses, like Snowflake, Databricks, Postgres, Redshift, and BigQuery.