AWS Glue Studio to Redshift

Question

Hello everyone !

I am trying to import data from one RDS SQL Server to  Redshift but I have a query with a recursive cte like that :

```
WITH RECURSIVE table(n) AS (
    select 1
  UNION ALL
    SELECT n+1 FROM table WHERE n < 100
)
SELECT n FROM table;
```

On Redshift there is no error but if I execute the query in an aws glue job (Transform - SQL Query) then i get this error :

*ParseException: no viable alternative at input 'WITH RECURSIVE table*

What am I missing ?

Answer

Hello,

Thanks for your answer ! You are right glue doesn't support recursive function, thus i created a view in SQL server. Then i imported this view as a table in glue and replaced te recusrive function in the script ;)

Answer

Hello,

I would like to inform you that Glue/Spark doesn't support recursive query. But you can use spark dataframe with the wildcard to load data from the recursive way.

Let say you have data inside the following s3 path: s3://kg-testing/test2/2018/07/10/\*.files.

Then you can use the following glue code to read it recursively using wildcard (*).

```
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

## @params: [JOB_NAME]
args = getResolvedOptions(sys.argv, ['JOB_NAME'])

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

print("**********DF printing*********")
spark.read .csv("s3://kg-testing/test2/*/*/*/*").createOrReplaceTempView("testsample")

sqlDF=spark.sql("select * from testsample")
print(sqlDF.show ())
```
In order for me to troubleshoot further, by taking a look at the logs in the backend, please feel free to open a support case with AWS using the following [link](https://console.aws.amazon.com/support/home#/case/create) with the sanitized script, the job run and we would be happy to help.

AWS Glue Studio to Redshift

Contenuto pertinente