Related questions

How to convert timestamp to AWS data lake s3 timestamp

2022-05-04 11:04 herder imported from Stackoverflow

In my data frame I have timestamp column with values in format: "%Y-%m-%d %H:%M:%m". I want to save this Data Frame to parquet using: df.write.partitionBy('p_timestamp').mode("overwrite").parquet('output/proto.parquet') and I want to have timestamp partition saved in AWS data lake s3 timestamp format: "%Y-%m-%dT%H-%M"

I tried:

df.select(F.col("p_timestamp"), F.date_format(F.col("p_timestamp"), "%Y-%m-%dT%H-%M").alias("date_format")).show()

but I got an error: Illegal pattern character 'T'

I would be grateful for any tips.

1 answer

  • answered 2022-05-04 11:29 fskj

    I think the formatting string you are looking for is:

    "yyyy-MM-dd'T'HH-mm"

    You get the error because the T is not quoted with ' to mark it as a string. You can consult sparks Datetime Patterns for Formatting and Parsing if you need something a little different.

    Small example:

    import pyspark.sql.types as T
schema = T.StructType([T.StructField("Time", T.StringType(), False)])
df = spark.createDataFrame([('1970-09-01 02:02:10',)], schema=schema)
df.withColumn("Convert", F.date_format(F.col("Time"), "yyyy-MM-dd'T'HH-mm")).show()

    Output:

    +-------------------+----------------+
|               Time|         Convert|
+-------------------+----------------+
|1970-09-01 02:03:10|1970-09-01T02-03|
+-------------------+----------------+

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • Upload file from html when block public access is true

    I am using django-s3direct to file upload

    https://github.com/bradleyg/django-s3direct

    Using IAM role setting because I upload the file from the server on ECS container.

    Now I set the blockPublicAccess of S3 false.

    When uploading images from html, there comes error.

    https://s3.ap-northeast-1.amazonaws.com/static-resource-v/images/c64d6e593de44aa5b10dcf1766582547/_origin.jpg?uploads (403 (Forbidden)
)

initiate error: static-resource-v/line-assets/images/c64d6e593de44aa5b10dcf1766582547/_origin.jpg AWS Code: AccessDenied, Message:Access Deniedstatus:403

    OK, it is understandable.

    Browser try to access the for initiation.

    However there is any way to upload file from browser when blockPublicAccess is true??

  • Assign ELB Account to S3 Bucket Policy

    I used the AWS console from the load balancer edit attributes screen and used it to create a bucket to use for access logging. I'm using this policy to form CDK code in typescript to stand up new S3 buckets to use for access logging in higher level environments where I cannot use the console. This is the policy I need to somehow form in typescript CDK code:

    "Statement": [
{
    "Effect":Allow",
    "Principal": {
        "AWS": "arn:--ELB-arnstuff--:root"
    },
    "Action": "s3:PutObject",
    "Resource": "arn:--S3-Bucket-arnstuff--/AWSLogs/123456789/*"
}
]

    I've managed to get the cdk code figured out to this point:

    bucket.addToResourcePolicy(
    new cdk.aws_iam.PolicyStatement({ 
      effect: awsIam.Effect.ALLOW,
      principals: //'**This is part I haven't figured out**',
      actions: ['s3:PutObject'],
      resources: ['${bucket.bucketArn}/*']
    })
);

    At this point I don't care if it's hard coded in the CDK, I just need something to help keep the ball rolling forward. Any help is appreciated, thanks

  • How can I get file metadata like the date created attribute from an S3 file?

    I have a lot of LogicPro files (.logicx) stored in an S3 bucket, and I want to extract the creation date from all of these files. This should not be the creation date of the object on s3, but the date for when it was created on my MacBook (the "Created" attribute in Finder).

    I've tried to retrieve the metadata from the object using the HEAD action:

    aws s3api head-object --bucket <my-bucket> --key <my-object-key>

    The output did not contain any information about the creation date of the actual file.

    {
   "AcceptRanges":"bytes",
   "LastModified":"2021-10-28T13:22:33+00:00",
   "ContentLength":713509,
   "ETag":"\"078c18ff0ab5322ada843a18bdd3914e\"",
   "VersionId":"9tseZuMRenKol1afntNM8mkRbeXo9n2W",
   "ContentType":"image/jpeg",
   "ServerSideEncryption":"AES256",
   "Metadata":{},
   "StorageClass":"STANDARD_IA"
}

    Is it possible to extract the file creation metadata attribute from an S3 object, without having to download the whole object?

  • spark-shell commands throwing error : “error: not found: value spark”

    problem screenshot

    :14: error: not found: value spark import spark.implicits._ ^ :14: error: not found: value spark import spark.sql ^

    here is my enviroment configuration. I different many times but I keep getting this error. Anyone knows the reason? I saw a similar question but the answers did not solve my problem.

    JAVA_HOME : C:\Program Files\Java\jdk1.8.0_51

    HADOOP_HOME : C:\Hadoop\winutils-master\hadoop-2.7.1

    SPARK_HOME : C:\Hadoop\spark-2.2.0-bin-hadoop2.7

    PATH :%JAVA_HOME%\bin;%SCALA_HOME%\bin;%HADOOP_HOME%\bin;%SPARK_HOME%\bin;

  • Computing number of business days between start/end columns

    I have two Dataframes

    • facts:
      • columns: data, start_date and end_date
    • holidays:
      • column: holiday_date

    What I want is a way to produce another Dataframe that has columns: data, start_date, end_date and num_holidays

    Where num_holidays is computed as: Number of days between start and end that are not weekends or holidays (as in the holidays table).

    The solution is here if we wanted to do this in PL/SQL. Crux is this part of code:

        --Calculate and return the number of workdays using the input parameters.
    --This is the meat of the function.
    --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
    RETURN (
        SELECT
        --Start with total number of days including weekends
        (DATEDIFF(dd,@StartDate, @EndDate)+1)
        --Subtact 2 days for each full weekend
        -(DATEDIFF(wk,@StartDate, @EndDate)*2)
        --If StartDate is a Sunday, Subtract 1
        -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
            THEN 1
            ELSE 0
        END)
        --If EndDate is a Saturday, Subtract 1
        -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
            THEN 1
            ELSE 0
        END)
        --Subtract all holidays
        -(Select Count(*) from [dbo].[tblHolidays]
          where  [HolDate] between @StartDate and @EndDate )
        )
    END

    I'm new to pyspark and was wondering what's the efficient way to do this? I can post the udf I'm writing if it helps though I'm going slow because I feel it's the wrong thing to do:

    • Is there a better way than creating a UDF that reads the holidays table in a Dataframe and joins with it to count the holidays? Can I even join inside a udf?
    • Is there a way to write a pandas_udf instead? Would it be faster enough?
    • Are there some optimizations I can apply like cache the holidays table somehow on every worker?
  • What is the right memory allocations that can be given to multiple spark streaming jobs if it is being processed in a single EMR cluster (m5.xlarge)?

    I have 12 spark streaming jobs and it receives a small size data at any time. These scripts has spark transformations and joins.

    What is the right memory allocations can be given to these spark streaming jobs if it is being processed in a single EMR cluster (m5.xlarge) (not using EMR steps) ? The memory allocations includes num-executors, executor-memory etc.

    Please explain the working of these spark jobs in the cluster. How will the cluster split resource to these jobs? Please help me with the basics.

  • How to execute the SQL job which is calling a SSIS package with ODBC connection?

    I'm running a job from SQL Server Management Studio 18 which is calling a SSIS package. In that package, we are exporting the records from Data lake to SQL and its working fine when I execute the package by double-click in the DB Server/Visual Studion, but its failing when executing the package from SQL Server Agent (SQL Server Agent -> Jobs -> Job -> Right click -> Select "Start job at step").

    Error: Code: 0xC0014020 Source: SSIS package Connection manager "odbc_connection" Description: An ODBC error -1 has occurred. Code: 0xC0014009 Source: SSIS package Connection manager "odbc_connection" Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. Code: 0x0000020F Description: The AcquireConnection method call to the connection manager odbc_connection failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    DTSX config: Dsn=odbc_connection;uid=uid;Password=password;

  • What Happens When a Delta Table is Created in Delta Lake?

    With the Databricks Lakehouse platform, it is possible to create 'tables' or to be more specific, delta tables using a statement such as the following,

    DROP TABLE IF EXISTS People10M;
CREATE TABLE People10M
USING parquet
OPTIONS (
  path "/mnt/training/dataframes/people-10m.parquet",
  header "true"
);

    What I would like to know is, what exactly happens behind the scenes when you create one of these tables? What exactly is a table in this context? Because the data is actually contained in files in data lake (data storage location) that delta lake is running on top of.. right? Are tables some kind of abstraction that allows us to access the data stored in these files using something like SQL?

    What does the USING parquet portion of this statement do? Are parquet tables different to CSV tables in some way? Or does this just depend on the format of the source data?

    Any links to material that explains this idea would be appreciated? I want to understand this in depth from a technical point of view.

  • Cost breakdown for a Cloud Data Lake Implementation

    We have a client in need of a data lake on the cloud.

    We need to provide the client the chance to breakdown costs between their areas in just one AWS Account.

    We are talking about query and data transfer costs also.