freestyle' SQL Execution

Question

So in MSSQL I can execute ad-hoc SQL statements e.g.

if exists (select 1 from sys.databases where [name] = 'Admin')
begin
    print('do something ');
    create schema lala;
    create table #temp(dummy nvarchar(128));
    insert into #temp(dummy) values('lala');
end

In Redshift I've been unable to do anything similar outside of a formal function or procedure.

Am I missing something, or is this just something that Redshift simply does not do ?

Thanks

Answer 1

I'd suggest you look at Lambda functions and Step Functions in AWS. Redshift is part of the larger ecosystem that is AWS and which Redshift supports some basic "operating" level coding, more advanced functionality is done at the ecosystem level. This means that not everything that can be done inside of other database is done inside of Redshift.

While this can be a bit of a learning curve the flexibility and power that can be enabled is quite large with this approach. For example a Lambda can perform the operations you mention in your example AND also add this schema and table to the Glue catalog, organize objects on S3 and enable Athena querying of these objects all in the same function. You can do what you describe in a stored procedure and the scope is just Redshift Or you perform this at the AWS level and update your data system across the entire data solution. A different way to view the division of work across systems.