Questions tagged with Database
Content language: English
Sort by most recent
Hi, I'd appreciate AWS Athena support for TIMESTAMP data type with microsecond precision for all row formats and table engines. Currently, the support is very inconsistent. See the SQL script below.
```
drop table if exists test_csv;
create external table if not exists test_csv (
id int,
created_time timestamp
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties('separatorChar'=',', 'quoteChar'='"', 'escapeChar'='\\')
location 's3://my-bucket/tmp/timestamp_csv_test/';
-- result: OK
drop table if exists test_parquet;
create external table if not exists test_parquet (
id int,
created_time timestamp
)
row format serde 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
stored as inputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
location 's3://my-bucket/tmp/timestamp_parquet_test/'
tblproperties ('parquet.compress' = 'snappy');
-- result: OK
drop table if exists test_iceberg;
create table if not exists test_iceberg (
id int,
created_time timestamp
)
location 's3://my-bucket/tmp/timestamp_iceberg_test/'
tblproperties ( 'table_type' ='iceberg');
-- result: OK
insert into test_csv values (1, timestamp '2023-03-22 11:00:00.123456');
/*
result: ERROR [HY000][100071] [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. GENERIC_INTERNAL_ERROR: class org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableIntObjectInspector cannot be cast to class org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector (org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableIntObjectInspector and org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector are in unnamed module of loader io.trino.server.PluginClassLoader @1df1bd44). If a data manifest file was generated at 's3://my-bucket/athena_results/ad44adee-2a80-4f41-906a-17aa5dc27730-manifest.csv', you may need to manually clean the data from locations specified in the manifest. Athena will not delete data in your account. [Execution ID: ***]
*/
insert into test_parquet values (1, timestamp '2023-03-22 11:00:00.123456');
-- result: OK
select * from test_parquet;
-- result: OK DATA: 1,2023-03-22 11:00:00.123000 BUT THE TIMESTAMP VALUE IS TRUNCATED TO MILLISECONDS!
insert into test_iceberg values (1, timestamp '2023-03-22 11:00:00.123456');
-- result: OK
select * from test_csv;
select * from test_iceberg;
-- result: OK DATA: 1,2023-03-22 11:00:00.123456 THIS IS FINE
```
Hello,
Im trying to setup DAX to handle caching for our DynamoDB logic for our existing kubernetes cluster.
However, when I follow the guides, they are incomplete.
From official doc here:
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DAX.create-cluster.console.create-subnet-group.html
1. Open the **DynamoDB **console at https://console.aws.amazon.com/dynamodb/.
2. In the navigation pane, under **DAX, choose Subnet groups.**
However there are NO such thing as "DAX" under DynamoDB. There is simply create table etc. When I search DAX in the console, I get no hits.
How exactly am I to understand how this is to be done when the official guide itself isnt correct?
Same with guides I've found, they simply do not align with how it looks in real life.
Help much appreciated since our Prod enviroment is in dire need of this ASAP.
Kind regards
Olle
[This document](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process) indicates that PostGIS needs to be updated before a major version upgrade. And, [this list](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-15x) shows the supported PostGIS version under PostgreSQL 15.2 is 3.3.2.
However, the latest PostGIS version available on RDS under PostgreSQL 14.7 [appears to be 3.1.7](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-14x). Indeed, after running this:
`SELECT postgis_extensions_upgrade();`
Then, attempting the upgrade, I get the following:
```
------------------------------------------------------------------
Upgrade could not be run on ...
------------------------------------------------------------------
The instance could not be upgraded from 14.7.R1 to 15.2.R1 because of following reasons. Please take appropriate action on databases that have usages incompatible with requested major engine version upgrade and try again.
- Following usages in database 'postgres' need to be corrected before upgrade:
-- The instance could not be upgraded because there are one or more databases with an older version of PostGIS extension or its dependent extensions (address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster) installed. Please upgrade all installations of PostGIS and drop its dependent extensions and try again.
----------------------- END OF LOG ----------------------
```
Trying the specific target version also fails:
`ALTER EXTENSION postgis UPDATE TO '3.3.2';`
```
ERROR: extension "postgis" has no update path from version "3.1.7" to version "3.3.2"
SQL state: 22023
```
Any suggestions?
Hi community,
I am trying to perform an ETL job using AWS Glue.
Our data is stored in MongoDB Atlas, inside a VPC.
Our AWS is connected to our MongoDB Atlas using VPC peering.
To perform the ETL job in AWS Glue I have first created a connection using the VPC details and the mongoDB Atlas URI along with the password and username. The connection is used by the AWS Glue crawlers to extract the schema to AWS Data Catalog Tables.
This connection works!
However, I am then attempting to perform the actual ETL job using the following pySpark code:
#My Temp Variables
source_database="d*********a"
source_table_name="main_businesses
source_mongodb_db_name = "main"
source_mongodb_collection = "businesses"
glueContext.create_dynamic_frame.from_catalog(database=source_database,table_name=source_table_name,additional_options = {"database": source_mongodb_db_name,"collection":source_mongodb_collection})
However the connection times out and for some reason mongodb atlas is blocking the connection from the ETL job.
It's as if the ETL Job is using the connection differently than the crawler does. Maybe the ETL Job is not able to run the job inside our AWS VPC that is connected to the MongoDB Atlas VPC (VPC Peering is not possible?).
Does anyone have any idea what might be going on or how I can fix this?
Thank you!
Hi,
Im getting this strange error:
```
ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation
```
Trying to run this command on "writer instance" of "multi-az cluster":
```
grant replication slave, replication client, replication_slave_admin on *.* to 'repl'@'%';
```
Knowing that the same command works on the "multi-az" "writer instance", im confused.
Hi,
Logged on as the m aster user on a Oracle 19 RDS database, I would like to execute the following:
GRANT EXECUTE ON XDB.DBMS_XMLSCHEMA TO appl_owner;
This fails with ORA-01031: insufficient privileges.
I tried this with RDSADMIN package:
EXECUTE rdsadmin.rdsadmin_util.**grant_sys_object**( p_obj_name=>'DBMS_XMLSCHEMA', p_grantee=>'FWD_OWNER', p_privilege=>'EXECUTE');
But that want to grant privileges on SYS objects, so it doesn't find objects in schema XDB.
How can I grant privileges on objects in different schema (xdb) to o different user/schema?
Thanks
I want to make some database operations with GUI and what is the best option for this project? To run my project i'm using xampp and I would like to be able to run queries on my database and dynamically display, sort, add, remove, and update records based on site user input. Should I use a RDS database or EC2?
Someone deleted rows from the critical table of RDS Aurora MySQL Instance. We don't have anything in slow query log either. Is there any other way to find out or trace all the query that ran against RDS aurora Instances?
Hello,
I want to implement "active-active" replication, like it is described here https://workmarket.tech/zero-downtime-maintenances-on-mysql-rds-ba13b51103c2 , but using multi-az feature created read instances/replicas. As far as i understood, if im using just "multi-az" then i dont have access to the created "secondary" database. In "multi-az cluster" and "aurora cluster", i do have "read" access to the "secondary" database replicas. Is it possible to get "cmd" access into them? Is it possible to implement some manual changes with cmd access? Is it possible to get the "binlog" location, and using it, setup the "active-active" replication, between the "primary" and "standby"? Or there are some limitations, that make it impossible?
Thanks
I'm newbie to AWS. When I looked at my Bill today, the RDS Backup services were the cause of the rise in the bill.
There isn't a database,nor snapshots or anything operating at the RDS Dashboard, only the standard mysql:80.
How can I terminate the RDS Backup Services?
All my instances,services,rds snapshots are deleted still there is increase in my bill.
We recently upgraded an MSSQL RDS database instance from 12.00.6329.1.v1 to 15.00.4236.7.v1. The instance functions fine, but it is supporting a legacy application that sometimes encounters errors that leave us no choice but to rollback to a snapshot. We also copy snapshots to our QA account periodically to refresh our QA databases, and this procedure has increased by the same significant amount of time. Prior to upgrading, these snapshot restores took around 30 minutes to get the instance to the Available state. After upgrading, it is taking several hours. The most recent attempt took 6 hours. This is harmful to our business as the increase in recovery time causes cascading effects. This feels like a possible AWS bug, I'm not sure what we could possibly do differently to improve this outcome. Any ideas?
Instance details:
Instance class: db.r6i.4xlarge
Storage type: gp2
Storage size: 2000GiB