Snow sql convertor displaying issues

[YaroslavLitvinov]

select 
    a.*,
    row_number() over (partition by a.event_id order by a.collector_tstamp, a.dvce_created_tstamp) as row_num from snowplow.public.events_iceberg a limit 1;

Above query succesfully returning results in json format when executed via /ui/query API.
But the same query executed from snow sql gets following results:

No column converter found for type: FLOAT32
No column converter found for type: FLOAT32
No column converter found for type: FLOAT32
+-------------------------------------------------------------------------------
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
+-------------------------------------------------------------------------------

Instead when snow sql --format=json specified it returns mostly expected result with only difference how it represents timestamp.
In /ui/query:
"etl_tstamp": "2022-08-20T00:39:32.000913"

In snow sql --format=json:
"etl_tstamp": "1660955972.913000"

[osipovartem]

It should be fixed in conversion logic, assign to me, I will fix

[YaroslavLitvinov]

/ui/query result:

[{
    "app_id": "website",
    "platform": "web",
    "etl_tstamp": "1660955972.913000",
    "collector_tstamp": "1660955972.261000",
    "dvce_created_tstamp": "1660955972.178000",
    "event": "struct",
    "event_id": "000c0134-c96a-4fe1-bc10-3d0898003f54",
    "txn_id": null,
    "name_tracker": "biz1",
    "v_tracker": "js-3.5.0",
    "v_collector": "ssc-2.6.1-kinesis",
    "v_etl": "snowplow-stream-enrich-3.2.3-common-3.2.3",
    "user_id": null,
    "user_ipaddress": "8dbe5e9e1ea6a6061e54e5a1659a13fef17f187cbcf2d1b54e5a7177ccd39f14",
    "user_fingerprint": null,
    "domain_userid": "e94c4a06-6ba1-47fc-9967-a882431ba615",
    "domain_sessionidx": 1,
    "network_userid": "7b0388e4-6e1d-43f2-ba56-88ed849aa83f",
    "geo_country": "US",
    "geo_region": "MO",
    "geo_city": "Kansas City",
    "geo_zipcode": "64157",
    "geo_latitude": 39.2792,
    "geo_longitude": -94.4608,
    "geo_region_name": "Missouri",
    "ip_isp": "Google Fiber",
    "ip_organization": "Google Fiber",
    "ip_domain": "googlefiber.net",
    "ip_netspeed": null,
    "page_url": "https://snowplowanalytics.com/resources/data-utilization-monetize-data/?&utm_source=google-ad&utm_medium=ppc&utm_campaign=data-utilization&utm_content=15064435094&utm_term=data%20monetize&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE",
    "page_title": null,
    "page_referrer": "https://www.google.com/",
    "page_urlscheme": "https",
    "page_urlhost": "snowplowanalytics.com",
    "page_urlport": 443,
    "page_urlpath": "/resources/data-utilization-monetize-data/",
    "page_urlquery": "&utm_source=google-ad&utm_medium=ppc&utm_campaign=data-utilization&utm_content=15064435094&utm_term=data%20monetize&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE",
    "page_urlfragment": null,
    "refr_urlscheme": "https",
    "refr_urlhost": "www.google.com",
    "refr_urlport": 443,
    "refr_urlpath": "/",
    "refr_urlquery": null,
    "refr_urlfragment": null,
    "refr_medium": "search",
    "refr_source": "Google",
    "refr_term": null,
    "mkt_medium": "ppc",
    "mkt_source": "google-ad",
    "mkt_term": "data monetize",
    "mkt_content": "15064435094",
    "mkt_campaign": "data-utilization",
    "se_category": "identity",
    "se_action": "snplow5.domain_userid",
    "se_label": "ae679cba-d356-4d49-a2f4-78cecf560b86",
    "se_property": null,
    "se_value": null,
    "tr_orderid": null,
    "tr_affiliation": null,
    "tr_total": null,
    "tr_tax": null,
    "tr_shipping": null,
    "tr_city": null,
    "tr_state": null,
    "tr_country": null,
    "ti_orderid": null,
    "ti_sku": null,
    "ti_name": null,
    "ti_category": null,
    "ti_price": null,
    "ti_quantity": null,
    "pp_xoffset_min": null,
    "pp_xoffset_max": null,
    "pp_yoffset_min": null,
    "pp_yoffset_max": null,
    "useragent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36",
    "br_name": null,
    "br_family": null,
    "br_version": null,
    "br_type": null,
    "br_renderengine": null,
    "br_lang": "en-US",
    "br_features_pdf": null,
    "br_features_flash": null,
    "br_features_java": null,
    "br_features_director": null,
    "br_features_quicktime": null,
    "br_features_realplayer": null,
    "br_features_windowsmedia": null,
    "br_features_gears": null,
    "br_features_silverlight": null,
    "br_cookies": true,
    "br_colordepth": "24",
    "br_viewwidth": 0,
    "br_viewheight": 0,
    "os_name": null,
    "os_family": null,
    "os_manufacturer": null,
    "os_timezone": "America/Chicago",
    "dvce_type": null,
    "dvce_ismobile": null,
    "dvce_screenwidth": 1920,
    "dvce_screenheight": 1080,
    "doc_charset": "UTF-8",
    "doc_width": 0,
    "doc_height": 0,
    "tr_currency": null,
    "tr_total_base": null,
    "tr_tax_base": null,
    "tr_shipping_base": null,
    "ti_currency": null,
    "ti_price_base": null,
    "base_currency": null,
    "geo_timezone": "America/Chicago",
    "mkt_clickid": "CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE",
    "mkt_network": "Google",
    "etl_tags": null,
    "dvce_sent_tstamp": "1660955972.186000",
    "refr_domain_userid": null,
    "refr_dvce_tstamp": null,
    "domain_sessionid": "24a71738-d156-4a61-87aa-f67c7a577b23",
    "derived_tstamp": "1660955972.253000",
    "event_vendor": "com.google.analytics",
    "event_name": "event",
    "event_format": "jsonschema",
    "event_version": "1-0-0",
    "event_fingerprint": "339a288325e7f534668795b9bc03644f",
    "true_tstamp": null,
    "load_tstamp": null,
    "contexts_com_snowplowanalytics_snowplow_ua_parser_context_1": "[{\"deviceFamily\":\"Other\",\"osFamily\":\"Windows\",\"osMajor\":\"10\",\"osPatchMinor\":\"1\",\"osVersion\":\"Windows 10\",\"useragentFamily\":\"Chrome\",\"useragentMajor\":\"104\",\"useragentMinor\":\"0\",\"useragentPatch\":\"0\",\"useragentVersion\":\"Chrome 104.0.0\"}]",
    "contexts_com_snowplowanalytics_snowplow_web_page_1": "[{\"id\":\"a0cc363a-55d3-41bb-a4cf-694849519609\"}]",
    "contexts_com_iab_snowplow_spiders_and_robots_1": "[{\"category\":\"BROWSER\",\"primaryImpact\":\"NONE\",\"reason\":\"PASSED_ALL\",\"spiderOrRobot\":false}]",
    "contexts_nl_basjes_yauaa_context_1": "[{\"agentClass\":\"Browser\",\"agentName\":\"Chrome\",\"agentNameVersion\":\"Chrome 104\",\"agentNameVersionMajor\":\"Chrome 104\",\"agentVersion\":\"104\",\"agentVersionMajor\":\"104\",\"deviceBrand\":\"Unknown\",\"deviceClass\":\"Desktop\",\"deviceCpu\":\"Intel x86_64\",\"deviceCpuBits\":\"64\",\"deviceName\":\"Desktop\",\"layoutEngineClass\":\"Browser\",\"layoutEngineName\":\"Blink\",\"layoutEngineNameVersion\":\"Blink 104.0\",\"layoutEngineNameVersionMajor\":\"Blink 104\",\"layoutEngineVersion\":\"104.0\",\"layoutEngineVersionMajor\":\"104\",\"operatingSystemClass\":\"Desktop\",\"operatingSystemName\":\"Unknown\",\"operatingSystemNameVersion\":\"Unknown ??\",\"operatingSystemNameVersionMajor\":\"Unknown ??\",\"operatingSystemVersion\":\"??\",\"operatingSystemVersionMajor\":\"??\"}]",
    "row_num": 1
}]

[YaroslavLitvinov]

in snow sql --format result

[
    {
        "app_id": "website",
        "platform": "web",
        "etl_tstamp": "2022-08-20T00:39:32.000913",
        "collector_tstamp": "2022-08-20T00:39:32.000261",
        "dvce_created_tstamp": "2022-08-20T00:39:32.000178",
        "event": "struct",
        "event_id": "000c0134-c96a-4fe1-bc10-3d0898003f54",
        "txn_id": null,
        "name_tracker": "biz1",
        "v_tracker": "js-3.5.0",
        "v_collector": "ssc-2.6.1-kinesis",
        "v_etl": "snowplow-stream-enrich-3.2.3-common-3.2.3",
        "user_id": null,
        "user_ipaddress": "8dbe5e9e1ea6a6061e54e5a1659a13fef17f187cbcf2d1b54e5a7177ccd39f14",
        "user_fingerprint": null,
        "domain_userid": "e94c4a06-6ba1-47fc-9967-a882431ba615",
        "domain_sessionidx": 1,
        "network_userid": "7b0388e4-6e1d-43f2-ba56-88ed849aa83f",
        "geo_country": "US",
        "geo_region": "MO",
        "geo_city": "Kansas City",
        "geo_zipcode": "64157",
        "geo_latitude": 39.2792,
        "geo_longitude": -94.4608,
        "geo_region_name": "Missouri",
        "ip_isp": "Google Fiber",
        "ip_organization": "Google Fiber",
        "ip_domain": "googlefiber.net",
        "ip_netspeed": null,
        "page_url": "https://snowplowanalytics.com/resources/data-utilization-monetize-data/?&utm_source=google-ad&utm_medium=ppc&utm_campaign=data-utilization&utm_content=15064435094&utm_term=data%20monetize&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE",
        "page_title": null,
        "page_referrer": "https://www.google.com/",
        "page_urlscheme": "https",
        "page_urlhost": "snowplowanalytics.com",
        "page_urlport": 443,
        "page_urlpath": "/resources/data-utilization-monetize-data/",
        "page_urlquery": "&utm_source=google-ad&utm_medium=ppc&utm_campaign=data-utilization&utm_content=15064435094&utm_term=data%20monetize&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE&gclid=CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE",
        "page_urlfragment": null,
        "refr_urlscheme": "https",
        "refr_urlhost": "www.google.com",
        "refr_urlport": 443,
        "refr_urlpath": "/",
        "refr_urlquery": null,
        "refr_urlfragment": null,
        "refr_medium": "search",
        "refr_source": "Google",
        "refr_term": null,
        "mkt_medium": "ppc",
        "mkt_source": "google-ad",
        "mkt_term": "data monetize",
        "mkt_content": "15064435094",
        "mkt_campaign": "data-utilization",
        "se_category": "identity",
        "se_action": "snplow5.domain_userid",
        "se_label": "ae679cba-d356-4d49-a2f4-78cecf560b86",
        "se_property": null,
        "se_value": null,
        "tr_orderid": null,
        "tr_affiliation": null,
        "tr_total": null,
        "tr_tax": null,
        "tr_shipping": null,
        "tr_city": null,
        "tr_state": null,
        "tr_country": null,
        "ti_orderid": null,
        "ti_sku": null,
        "ti_name": null,
        "ti_category": null,
        "ti_price": null,
        "ti_quantity": null,
        "pp_xoffset_min": null,
        "pp_xoffset_max": null,
        "pp_yoffset_min": null,
        "pp_yoffset_max": null,
        "useragent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36",
        "br_name": null,
        "br_family": null,
        "br_version": null,
        "br_type": null,
        "br_renderengine": null,
        "br_lang": "en-US",
        "br_features_pdf": null,
        "br_features_flash": null,
        "br_features_java": null,
        "br_features_director": null,
        "br_features_quicktime": null,
        "br_features_realplayer": null,
        "br_features_windowsmedia": null,
        "br_features_gears": null,
        "br_features_silverlight": null,
        "br_cookies": false,
        "br_colordepth": "24",
        "br_viewwidth": 0,
        "br_viewheight": 0,
        "os_name": null,
        "os_family": null,
        "os_manufacturer": null,
        "os_timezone": "America/Chicago",
        "dvce_type": null,
        "dvce_ismobile": null,
        "dvce_screenwidth": 1920,
        "dvce_screenheight": 1080,
        "doc_charset": "UTF-8",
        "doc_width": 0,
        "doc_height": 0,
        "tr_currency": null,
        "tr_total_base": null,
        "tr_tax_base": null,
        "tr_shipping_base": null,
        "ti_currency": null,
        "ti_price_base": null,
        "base_currency": null,
        "geo_timezone": "America/Chicago",
        "mkt_clickid": "CjwKCAjw6fyXBhBgEiwAhhiZstxrvF2s4Rs3TMqQ41fzKxJX0iz5BgLoBgixUUCYJSHxV7n2JiEZYhoCM9QQAvD_BwE",
        "mkt_network": "Google",
        "etl_tags": null,
        "dvce_sent_tstamp": "2022-08-20T00:39:32.000186",
        "refr_domain_userid": null,
        "refr_dvce_tstamp": null,
        "domain_sessionid": "24a71738-d156-4a61-87aa-f67c7a577b23",
        "derived_tstamp": "2022-08-20T00:39:32.000253",
        "event_vendor": "com.google.analytics",
        "event_name": "event",
        "event_format": "jsonschema",
        "event_version": "1-0-0",
        "event_fingerprint": "339a288325e7f534668795b9bc03644f",
        "true_tstamp": null,
        "load_tstamp": null,
        "contexts_com_snowplowanalytics_snowplow_ua_parser_context_1": "[{\"deviceFamily\":\"Other\",\"osFamily\":\"Windows\",\"osMajor\":\"10\",\"osPatchMinor\":\"1\",\"osVersion\":\"Windows 10\",\"useragentFamily\":\"Chrome\",\"useragentMajor\":\"104\",\"useragentMinor\":\"0\",\"useragentPatch\":\"0\",\"useragentVersion\":\"Chrome 104.0.0\"}]",
        "contexts_com_snowplowanalytics_snowplow_web_page_1": "[{\"id\":\"a0cc363a-55d3-41bb-a4cf-694849519609\"}]",
        "contexts_com_iab_snowplow_spiders_and_robots_1": "[{\"category\":\"BROWSER\",\"primaryImpact\":\"NONE\",\"reason\":\"PASSED_ALL\",\"spiderOrRobot\":false}]",
        "contexts_nl_basjes_yauaa_context_1": "[{\"agentClass\":\"Browser\",\"agentName\":\"Chrome\",\"agentNameVersion\":\"Chrome 104\",\"agentNameVersionMajor\":\"Chrome 104\",\"agentVersion\":\"104\",\"agentVersionMajor\":\"104\",\"deviceBrand\":\"Unknown\",\"deviceClass\":\"Desktop\",\"deviceCpu\":\"Intel x86_64\",\"deviceCpuBits\":\"64\",\"deviceName\":\"Desktop\",\"layoutEngineClass\":\"Browser\",\"layoutEngineName\":\"Blink\",\"layoutEngineNameVersion\":\"Blink 104.0\",\"layoutEngineNameVersionMajor\":\"Blink 104\",\"layoutEngineVersion\":\"104.0\",\"layoutEngineVersionMajor\":\"104\",\"operatingSystemClass\":\"Desktop\",\"operatingSystemName\":\"Unknown\",\"operatingSystemNameVersion\":\"Unknown ??\",\"operatingSystemNameVersionMajor\":\"Unknown ??\",\"operatingSystemVersion\":\"??\",\"operatingSystemVersionMajor\":\"??\"}]",
        "row_num": 1
    }
]

[osipovartem]

snowflake python connector for json type use this convertor for TIMESTAMP_NTZ

def create_timestamp_from_string(
        value: str,
        scale: int,
) -> datetime:
    seconds, fraction = SnowflakeConverter.get_seconds_microseconds(
        value=value, scale=scale
    )
    return datetime.fromtimestamp(seconds, timezone.utc).replace(
        tzinfo=None
    ) + timedelta(microseconds=fraction)

create_timestamp_from_string("2022-08-19-00:00:00.000000", 0)

which triggers

    seconds = int(lhs)
              ^^^^^^^^
ValueError: invalid literal for int() with base 10: '2022-08-19-00:00:00'

Related #67