Amazon Redshift Serverless

1. Open https://portal.aws.amazon.com/billing/signup.

2. Follow the online instructions.

   When you sign up for an AWS account, an AWS account root user is created. The root user has access to all AWS services and resources in the account. As a security best practice, assign administrative access to an administrative user, and use only the root user to perform tasks that require root user access.

To configure with default settings:

1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshiftv2/.

   Choose Try Redshift Serverless Free Trial.

2. Under Configuration, choose Use default settings. Amazon Redshift Serverless creates a default namespace with a default workgroup associated with this namespace. Choose Save configuration.

   Note

   A Namespace is a collection of database objects and users. Namespaces group together all of the resources you use in Redshift Serverless, such as schemas, tables, users, datashares, and snapshots.

   A Workgroup is a collection of compute resources. Workgroups house compute resources that Redshift Serverless uses to run computational tasks.

   The following screenshot shows the default settings for Amazon Redshift Serverless.

   

3. After setup completes, choose Continue to go to your Serverless dashboard. You can see that the serverless workgroup and namespace are available.

   

   Note

   If Redshift Serverless doesn't create the workgroup successfully, you can do the following:

   • Address any errors that Redshift Serverless reports, such as having too few subnets in your Amazon VPC.

   • Delete the namespace by choosing default-namespace in the Redshift Serverless dashboard, and then choosing Actions, Delete namespace. Deleting a namespace takes several minutes.

   • When you open the Redshift Serverless console again, the welcome screen appears.

1. To launch query editor v2 from the Amazon Redshift Serverless console, choose Query data. When you invoke query editor v2 from the Amazon Redshift Serverless console, a new browser tab opens with the query editor. The query editor v2 connects from your client machine to the Amazon Redshift Serverless environment.

   

2. For this guide, you'll use your AWS administrator account and the default AWS KMS key. For information about configuring the query editor v2, including which permissions are needed, see Configuring your AWS account in the Amazon Redshift Management Guide. For information about configuring Amazon Redshift to use a customer managed key, or to change the KMS key that Amazon Redshift uses, see Changing the AWS KMS key for a namespace.

3. To connect to a workgroup, choose the workgroup name in the tree-view panel.

   

4. When connecting to a new workgroup for the first time within query editor v2, you must select the type of authentication to use to connect to the workgroup. For this guide, leave Federated user selected, and choose Create connection.

   

   Once you are connected, you can choose to load sample data from Amazon Redshift Serverless or from an Amazon S3 bucket.

5. Under the Amazon Redshift Serverless default workgroup, expand the sample_data_dev database. There are three sample schemas corresponding to three sample datasets that you can load into the Amazon Redshift Serverless database. Choose the sample dataset that you want to load, and choose Open sample notebooks.

   

   Note

   A SQL notebook is a container for SQL and Markdown cells. You can use notebooks to organize, annotate, and share multiple SQL commands in a single document.

6. When loading data for the first time, query editor v2 will prompt you to create a sample database. Choose Create.

   

• Once Amazon Redshift Serverless finishes loading the sample data, all of the sample queries are loaded in the editor. You can choose Run all to run all of the queries from the sample notebooks.

  

  You can also export the results as a JSON or CSV file or view the results in a chart.

  

1. Before you can load data from Amazon S3, you must first create an IAM role with the necessary permissions and attach it to your serverless namespace. To do so, choose Namespace configuration from the navigation menu, choose your namespace, and then choose Security and encryption. Then, choose Manage IAM roles.

   

2. Expand the Manage IAM roles menu, and choose Create IAM role.

   

3. Choose the level of S3 bucket access that you want to grant to this role, and choose Create IAM role as default.

   

4. Choose Save changes. You can now load sample data from Amazon S3.

Load sample data from Amazon S3

1. In query editor v2, choose Add, then choose Notebook to create a new SQL notebook.

   

2. Switch to the dev database.

   

3. Create tables.

   If you are using the query editor v2, copy and run the following create table statements to create tables in the dev database. For more information about the syntax, see CREATE TABLE in the Amazon Redshift Database Developer Guide.

   create table users(
   userid integer not null distkey sortkey,
   username char(8),
   firstname varchar(30),
   lastname varchar(30),
   city varchar(30),
   state char(2),
   email varchar(100),
   phone char(14),
   likesports boolean,
   liketheatre boolean,
   likeconcerts boolean,
   likejazz boolean,
   likeclassical boolean,
   likeopera boolean,
   likerock boolean,
   likevegas boolean,
   likebroadway boolean,
   likemusicals boolean);                        
   
   create table event(
   eventid integer not null distkey,
   venueid smallint not null,
   catid smallint not null,
   dateid smallint not null sortkey,
   eventname varchar(200),
   starttime timestamp);
   
   create table sales(
   salesid integer not null,
   listid integer not null distkey,
   sellerid integer not null,
   buyerid integer not null,
   eventid integer not null,
   dateid smallint not null sortkey,
   qtysold smallint not null,
   pricepaid decimal(8,2),
   commission decimal(8,2),
   saletime timestamp);
   

4. In the query editor v2, create a new SQL cell in your notebook.

   

5. Now use the COPY command in query editor v2 to load large datasets from Amazon S3 or Amazon DynamoDB into Amazon Redshift. For more information about COPY syntax, see COPY in the Amazon Redshift Database Developer Guide.

   You can run the COPY command with some sample data available in a public S3 bucket. Run the following SQL commands in the query editor v2.

   COPY users 
   FROM 's3://redshift-downloads/tickit/allusers_pipe.txt' 
   DELIMITER '|' 
   TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
   IGNOREHEADER 1 
   REGION 'us-east-1'
   IAM_ROLE default;                    
                       
   COPY event
   FROM 's3://redshift-downloads/tickit/allevents_pipe.txt' 
   DELIMITER '|' 
   TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
   IGNOREHEADER 1 
   REGION 'us-east-1'
   IAM_ROLE default;
   
   COPY sales
   FROM 's3://redshift-downloads/tickit/sales_tab.txt' 
   DELIMITER '\t' 
   TIMEFORMAT 'MM/DD/YYYY HH:MI:SS'
   IGNOREHEADER 1 
   REGION 'us-east-1'
   IAM_ROLE default;
   

6. After loading data, create another SQL cell in your notebook and try some example queries. For more information on working with the SELECT command, see SELECT in the Amazon Redshift Developer Guide. To understand the sample data's structure and schemas, explore using the query editor v2.

   -- Find top 10 buyers by quantity.
   SELECT firstname, lastname, total_quantity 
   FROM   (SELECT buyerid, sum(qtysold) total_quantity
           FROM  sales
           GROUP BY buyerid
           ORDER BY total_quantity desc limit 10) Q, users
   WHERE Q.buyerid = userid
   ORDER BY Q.total_quantity desc;
   
   -- Find events in the 99.9 percentile in terms of all time gross sales.
   SELECT eventname, total_price 
   FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile 
          FROM (SELECT eventid, sum(pricepaid) total_price
                FROM   sales
                GROUP BY eventid)) Q, event E
          WHERE Q.eventid = E.eventid
          AND percentile = 1
   ORDER BY total_price desc;