Un SerD molto preciso

mysql --host=database-exercise2.xxxxxx.eu-north-1.rds.amazonaws.com --user=yyyyyy--password=zzzzzz

{
    "user_id":"{{random.number(
        {
            "min":1,
            "max":1000
        }
    )}}",
    "page":"{{random.arrayElement(
        ["www.recuperosociale.it/blog","www.recuperosociale.it/blog/problema1","www.recuperosociale.it/blog/problema1/sottopagina1","www.recuperosociale.it/blog/problema1/sottopagina2","www.recuperosociale.it/blog/problema1/sottopagina3","www.recuperosociale.it/blog/problema1/sottopagina4","www.recuperosociale.it/blog/problema1/sottopagina5","www.recuperosociale.it/blog/problema2/sottopagina1","www.recuperosociale.it/blog/problema2/sottopagina2","www.recuperosociale.it/blog/problema2/sottopagina3","www.recuperosociale.it/blog/problema2/sottopagina4","www.recuperosociale.it/blog/problema2/sottopagina5","www.recuperosociale.it/blog/problema3/sottopagina1","www.recuperosociale.it/blog/problema3/sottopagina2","www.recuperosociale.it/blog/problema3/sottopagina3","www.recuperosociale.it/blog/problema3/sottopagina4","www.recuperosociale.it/blog/problema3/sottopagina5","www.recuperosociale.it/blog/problema4/sottopagina1","www.recuperosociale.it/blog/problema4/sottopagina2","www.recuperosociale.it/blog/problema4/sottopagina3","www.recuperosociale.it/blog/problema4/sottopagina4","www.recuperosociale.it/blog/problema4/sottopagina5","www.recuperosociale.it/blog/problema5/sottopagina1","www.recuperosociale.it/blog/problema5/sottopagina2","www.recuperosociale.it/blog/problema5/sottopagina3","www.recuperosociale.it/blog/problema5/sottopagina4","www.recuperosociale.it/blog/problema5/sottopagina5","www.recuperosociale.it/servizi/servizio1","www.recuperosociale.it/servizi/servizio2","www.recuperosociale.it/servizi/servizio3","www.recuperosociale.it/servizi/servizio4","www.recuperosociale.it/servizi/servizio5","www.recuperosociale.it/documento/descrizione_servizi","www.recuperosociale.it/guide","www.recuperosociale.it/guide/disposizione_oggetti_casa","www.recuperosociale.it/guide/circoli_viziosi","www.recuperosociale.it/guide/sport","www.recuperosociale.it/guide/benessere_alimentare","www.recuperosociale.it/guide/pronto_aiuto","www.recuperosociale.it/guide/pronto_aiuto/problema1","www.recuperosociale.it/guide/pronto_aiuto/problema2","www.recuperosociale.it/guide/pronto_aiuto/problema3","www.recuperosociale.it/contatti","www.recuperosociale.it/contatti/il_nostro_stabile","www.recuperosociale.it/storie_pazienti/problema1/storia1","www.recuperosociale.it/storie_pazienti/problema1/storia2","www.recuperosociale.it/storie_pazienti/problema1/storia3","www.recuperosociale.it/storie_pazienti/problema1/storia4","www.recuperosociale.it/storie_pazienti/problema1/storia5","www.recuperosociale.it/storie_pazienti/problema2/storia1","www.recuperosociale.it/storie_pazienti/problema2/storia2","www.recuperosociale.it/storie_pazienti/problema2/storia3","www.recuperosociale.it/storie_pazienti/problema2/storia4","www.recuperosociale.it/storie_pazienti/problema2/storia5","www.recuperosociale.it/storie_pazienti/problema3/storia1","www.recuperosociale.it/storie_pazienti/problema3/storia2","www.recuperosociale.it/storie_pazienti/problema3/storia3","www.recuperosociale.it/storie_pazienti/problema3/storia4","www.recuperosociale.it/storie_pazienti/problema3/storia5","www.recuperosociale.it/storie_pazienti/problema4/storia1","www.recuperosociale.it/storie_pazienti/problema4/storia2","www.recuperosociale.it/storie_pazienti/problema4/storia3","www.recuperosociale.it/storie_pazienti/problema4/storia4","www.recuperosociale.it/storie_pazienti/problema4/storia5","www.recuperosociale.it/storie_pazienti/problema5/storia1","www.recuperosociale.it/storie_pazienti/problema5/storia2","www.recuperosociale.it/storie_pazienti/problema5/storia3","www.recuperosociale.it/storie_pazienti/problema5/storia4","www.recuperosociale.it/storie_pazienti/problema5/storia5"]
    )}}",
    "date":"Tue Feb 25 2024 08:56:16 GMT+0100 (Ora standard dell’Europa centrale)",
    "session_length":"{{random.number(
        {
            "min":1,
            "max":2500
        }
    )}}"
}

select DATE_FORMAT(TO_DATE(SUBSTRING(date, 5, 11), 'MMM dd yyyy'), 'yyyy-MM-dd') AS formatted_date, * from myDataSource

select * from myDataSource
WHERE formatted_date BETWEEN date_trunc('month', current_date) + interval '-2' month AND date_trunc('month', current_date) + interval '0' month - interval '1' day

with first_filter as (

    SELECT DISTINCT *
    FROM myDataSource
    WHERE user_id IN (
        SELECT user_id FROM myDataSource WHERE page = 'www.recuperosociale.it/contatti/il_nostro_stabile' AND session_length >= 60
    ) AND user_id IN (
        SELECT user_id FROM myDataSource WHERE page = 'www.recuperosociale.it/documento/descrizione_servizi'
    )

),

second_filter_prob1 as(

    SELECT DISTINCT user_id
    FROM first_filter
    WHERE user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema1/sottopagina1' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema1/sottopagina2' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema1/sottopagina3' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema1/sottopagina4' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema1/sottopagina5' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/guide/pronto_aiuto/problema1' AND session_length >= 1500
    ) AND user_id IN ( 
        SELECT user_id
            FROM (
                SELECT user_id, 
                       SUM(CASE WHEN page IN ('www.recuperosociale.it/storie_pazienti/problema1/storia1', 
                                              'www.recuperosociale.it/storie_pazienti/problema1/storia2', 
                                              'www.recuperosociale.it/storie_pazienti/problema1/storia3', 
                                              'www.recuperosociale.it/storie_pazienti/problema1/storia4', 
                                              'www.recuperosociale.it/storie_pazienti/problema1/storia5') 
                                AND session_length >= 900 THEN 1 ELSE 0 END) AS visits
                FROM myDataSource
                GROUP BY user_id
            )
            WHERE visits >= 3
    ) 

),

second_filter_prob2 as(

    SELECT DISTINCT user_id
    FROM first_filter
    WHERE user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema2/sottopagina1' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema2/sottopagina2' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema2/sottopagina3' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema2/sottopagina4' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema2/sottopagina5' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/guide/pronto_aiuto/problema2' AND session_length >= 1500
    ) AND user_id IN ( 
        SELECT user_id
            FROM (
                SELECT user_id, 
                       SUM(CASE WHEN page IN ('www.recuperosociale.it/storie_pazienti/problema2/storia1', 
                                              'www.recuperosociale.it/storie_pazienti/problema2/storia2', 
                                              'www.recuperosociale.it/storie_pazienti/problema2/storia3', 
                                              'www.recuperosociale.it/storie_pazienti/problema2/storia4', 
                                              'www.recuperosociale.it/storie_pazienti/problema2/storia5') 
                                AND session_length >= 900 THEN 1 ELSE 0 END) AS visits
                FROM myDataSource
                GROUP BY user_id
            )
            WHERE visits >= 3
    ) 

),


second_filter_prob3 as(

    SELECT DISTINCT user_id
    FROM first_filter
    WHERE user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema3/sottopagina1' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema3/sottopagina2' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema3/sottopagina3' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema3/sottopagina4' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/blog/problema3/sottopagina5' AND session_length >= 600
    ) AND user_id IN (
        SELECT user_id FROM first_filter WHERE page = 'www.recuperosociale.it/guide/pronto_aiuto/problema3' AND session_length >= 1500
    ) AND user_id IN ( 
        SELECT user_id
            FROM (
                SELECT user_id, 
                       SUM(CASE WHEN page IN ('www.recuperosociale.it/storie_pazienti/problema3/storia1', 
                                              'www.recuperosociale.it/storie_pazienti/problema3/storia2', 
                                              'www.recuperosociale.it/storie_pazienti/problema3/storia3', 
                                              'www.recuperosociale.it/storie_pazienti/problema3/storia4', 
                                              'www.recuperosociale.it/storie_pazienti/problema3/storia5') 
                                AND session_length >= 900 THEN 1 ELSE 0 END) AS visits
                FROM myDataSource
                GROUP BY user_id
            )
            WHERE visits >= 3
    ) 

)

SELECT 
    (SELECT ARRAY_AGG(user_id) FROM second_filter_prob1) AS probFirst,
    (SELECT ARRAY_AGG(user_id) FROM second_filter_prob2) AS probSecond,
    (SELECT ARRAY_AGG(user_id) FROM second_filter_prob3) AS probThird;

import boto3
import urllib.parse

s3 = boto3.client('s3')

def lambda_handler(event, context):
    # Get the source bucket and object key from the event
    source_bucket = event['Records'][0]['s3']['bucket']['name']
    source_key = urllib.parse.unquote_plus(event['Records'][0]['s3']['object']['key'])


    print("source_bucket: {source_bucket}")
    print("source_key: {source_key}")
    
    
    # Define the destination folder and filename
    destination_folder = 'recupero-sociale/prob1-users-csv'
    destination_filename = 'output_file.csv'

    # Define the destination key
    destination_key = f"{destination_folder}/{destination_filename}"

    try:
        # Copy the object to the new destination
        s3.copy_object(
            Bucket=source_bucket,
            CopySource={'Bucket': source_bucket, 'Key': source_key},
            Key=destination_key
        )
        print(f"File copied from {source_key} to {destination_key}")
        return {
            'statusCode': 200,
            'body': 'File copied successfully'
        }
    except Exception as e:
        print(f"Error copying file: {e}")
        return {
            'statusCode': 500,
            'body': f"Error copying file: {e}"
        }

import os.path
import boto3
import email
from botocore.exceptions import ClientError
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication


s3 = boto3.client("s3")

def lambda_handler(event, context):
    # Replace sender@example.com with your "From" address.
    # This address must be verified with Amazon SES.
    SENDER = "test@test.com"

    # Replace recipient@example.com with a "To" address. If your account 
    # is still in the sandbox, this address must be verified.
    RECIPIENT = "test@test.com"

    # Specify a configuration set. If you do not want to use a configuration
    # set, comment the following variable, and the 
    # ConfigurationSetName=CONFIGURATION_SET argument below.
    # CONFIGURATION_SET = "ConfigSet"

    AWS_REGION = "eu-north-1"
    SUBJECT = "Dati Recupero Sociale"

    # This is the start of the process to pull the files we need from the S3 bucket into the email.
    # Get the records for the triggered event
    FILEOBJ = event["Records"][0]
    # Extract the bucket name from the records for the triggered event
    BUCKET_NAME = str(FILEOBJ['s3']['bucket']['name'])
    # Extract the object key (basicaly the file name/path - note that in S3 there are 
    # no folders, the path is part of the name) from the records for the triggered event
    KEY = str(FILEOBJ['s3']['object']['key'])

    # extract just the last portion of the file name from the file. This is what the file
    # would have been called prior to being uploaded to the S3 bucket
    FILE_NAME = os.path.basename(KEY) 

    # Using the file name, create a new file location for the lambda. This has to
    # be in the tmp dir because that's the only place lambdas let you store up to
    # 500mb of stuff, hence the '/tmp/'+ prefix
    TMP_FILE_NAME = '/tmp/' +FILE_NAME

    # Download the file/s from the event (extracted above) to the tmp location
    s3.download_file(BUCKET_NAME, KEY, TMP_FILE_NAME)

    # Make explicit that the attachment will have the tmp file path/name. You could just
    # use the TMP_FILE_NAME in the statments below if you'd like.
    ATTACHMENT = TMP_FILE_NAME

    # The email body for recipients with non-HTML email clients.
    BODY_TEXT = "Altre persone hanno bisogno di te!,\r\nIn allegato trovo il CSV con i numeri di telefono ed email."

    # The HTML body of the email.
    BODY_HTML = """\
    <html>
    <head></head>
    <body>
    <h1>Altre persone hanno bisogno di te!</h1>
    <p>In allegato trovi il CSV con i numeri di telefono ed email.</p>
    </body>
    </html>
    """

    # The character encoding for the email.
    CHARSET = "utf-8"

    # Create a new SES resource and specify a region.
    client = boto3.client('ses',region_name=AWS_REGION)

    # Create a multipart/mixed parent container.
    msg = MIMEMultipart('mixed')
    # Add subject, from and to lines.
    msg['Subject'] = SUBJECT 
    msg['From'] = SENDER 
    msg['To'] = RECIPIENT

    # Create a multipart/alternative child container.
    msg_body = MIMEMultipart('alternative')

    # Encode the text and HTML content and set the character encoding. This step is
    # necessary if you're sending a message with characters outside the ASCII range.
    textpart = MIMEText(BODY_TEXT.encode(CHARSET), 'plain', CHARSET)
    htmlpart = MIMEText(BODY_HTML.encode(CHARSET), 'html', CHARSET)

    # Add the text and HTML parts to the child container.
    msg_body.attach(textpart)
    msg_body.attach(htmlpart)

    # Define the attachment part and encode it using MIMEApplication.
    att = MIMEApplication(open(ATTACHMENT, 'rb').read())

    # Add a header to tell the email client to treat this part as an attachment,
    # and to give the attachment a name.
    att.add_header('Content-Disposition','attachment',filename=os.path.basename(ATTACHMENT))

    # Attach the multipart/alternative child container to the multipart/mixed
    # parent container.
    msg.attach(msg_body)

    # Add the attachment to the parent container.
    msg.attach(att)
    print(msg)
    try:
        #Provide the contents of the email.
        response = client.send_raw_email(
            Source=SENDER,
            Destinations=[
                RECIPIENT
            ],
            RawMessage={
                'Data':msg.as_string(),
            },
    #        ConfigurationSetName=CONFIGURATION_SET
        )
    # Display an error if something goes wrong. 
    except ClientError as e:
        print(e.response['Error']['Message'])
    else:
        print("Email sent! Message ID:"),
        print(response['MessageId'])