def _create_athena_queries()

in Gems/AWSMetrics/cdv1/aws_metrics/batch_analytics.py [0:0]

• 53 lines of code
• 2 McCabe index (conditional complexity)

    def _create_athena_queries(self) -> None:
        """
        Create several example queries for reference.
        """
        self._named_queries = [
            athena.CfnNamedQuery(
                self._stack,
                id='NamedQuery-CreatePartitionedEventsJson',
                name=resource_name_sanitizer.sanitize_resource_name(
                    f'{self._stack.stack_name}-NamedQuery-CreatePartitionedEventsJson', 'athena_named_query'),
                database=self._events_database_name,
                query_string="CREATE TABLE events_json "
                             "WITH (format='JSON',partitioned_by=ARRAY['application_id']) "
                             "AS SELECT year, month, day, event_id, application_id "
                             f"FROM \"{self._events_database_name}\".\"{self._events_table_name}\"",
                description='This command demonstrates how to create a new table of raw events'
                            ' transformed to JSON format. Output is partitioned by Application',
                work_group=self._athena_work_group.name
            ),
            athena.CfnNamedQuery(
                self._stack,
                id='NamedQuery-TotalEventsLastMonth',
                name=resource_name_sanitizer.sanitize_resource_name(
                    f'{self._stack.stack_name}-NamedQuery-TotalEventsLastMonth', 'athena_named_query'),
                database=self._events_database_name,
                query_string="WITH detail AS "
                             "(SELECT date_trunc('month', date(date_parse(CONCAT(year, '-', month, '-', day), '%Y-%m-%d'))) as event_month, * "
                             f"FROM \"{self._events_database_name}\".\"{self._events_table_name}\") "
                             "SELECT "
                             "date_trunc('month', event_month) as month, application_id, count(DISTINCT event_id) as event_count "
                             "FROM detail "
                             "GROUP BY date_trunc('month', event_month), application_id",
                description='Total events over last month',
                work_group=self._athena_work_group.name
            ),
            athena.CfnNamedQuery(
                self._stack,
                id='NamedQuery-LoginLastMonth',
                name=resource_name_sanitizer.sanitize_resource_name(
                    f'{self._stack.stack_name}-NamedQuery-LoginLastMonth', 'athena_named_query'),
                database=self._events_database_name,
                query_string="WITH detail AS ("
                             "SELECT date_trunc('month', date(date_parse(CONCAT(year, '-', month, '-', day), '%Y-%m-%d'))) as event_month, * "
                             f"FROM \"{self._events_database_name}\".\"{self._events_table_name}\") "
                             "SELECT "
                             "date_trunc('month', event_month) as month, "
                             "count(*) as new_accounts "
                             "FROM detail "
                             "WHERE event_name = 'login' "
                             "GROUP BY date_trunc('month', event_month)",
                description='Total number of login events over the last month',
                work_group=self._athena_work_group.name
            )
        ]

        for named_query in self._named_queries:
            named_query.node.add_dependency(self._athena_work_group)