<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Brenda Varguez on Medium]]></title>
        <description><![CDATA[Stories by Brenda Varguez on Medium]]></description>
        <link>https://medium.com/@adrevant?source=rss-c824aca476f8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*NoR5KMBWYuEmT0MeX9_0og.jpeg</url>
            <title>Stories by Brenda Varguez on Medium</title>
            <link>https://medium.com/@adrevant?source=rss-c824aca476f8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 05:23:41 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@adrevant/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Mapping the Ocean, One Reef at a Time]]></title>
            <link>https://medium.com/@adrevant/mapping-the-ocean-one-reef-at-a-time-27daf80b46c4?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/27daf80b46c4</guid>
            <category><![CDATA[oceans]]></category>
            <category><![CDATA[sustainability]]></category>
            <category><![CDATA[ocean-conservation]]></category>
            <category><![CDATA[environment]]></category>
            <category><![CDATA[conservation]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Tue, 30 Sep 2025 21:30:29 GMT</pubDate>
            <atom:updated>2025-09-30T21:30:29.487Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>In my <a href="https://medium.com/@adrevant/introduction-to-the-ocean-data-platform-496736703355">previous blog post</a>, I introduced Hub Ocean and its groundbreaking Ocean Data Platform (ODP), a platform that makes ocean data more accessible to everyone. Today, we’ll dive deeper by exploring how to retrieve specific datasets from ODP and integrate it with OpenAI’s taxonomic classification capabilities, a method used to categorize organisms based on their characteristics, to identify and analyze coral reef ecosystems.</p><p>Coral reefs are among our planet’s most valuable and threatened ecosystems. Covering less than 0.1% of the ocean floor, they support approximately 25% of all marine species and provide critical resources to over 500 million people worldwide. However, climate change, pollution, and other human activities have led to unprecedented degradation of these ecosystems, with some estimates suggesting we’ve already lost 50% of the world’s coral reefs.</p><p>In this post, I’ll walk you through how I used the Ocean Data Platform (ODP) and OpenAI to explore and enrich a coral reef dataset. By the end, you’ll know how to:</p><ul><li>Retrieve and enrich an ocean dataset using ODP</li><li>Use geospatial tools to identify oceans and protected areas</li><li>Classify coral species using OpenAI’s API</li><li>Upload and share your results back to ODP</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/956/1*1rB9whB0N-2i3eZlopMLQA.jpeg" /><figcaption>Image generated by Gemini</figcaption></figure><h3>Exploring our data</h3><blockquote><em>The complete code for this tutorial, along with detailed documentation of the functions, can be found in </em><a href="https://github.com/adrevant/medium-lab"><em>this github repository</em></a><em> at </em><em>hub_ocean_tests/</em><em>coral_classification.ipynb</em></blockquote><p>Let’s start by retrieving the target dataset. I will use my modified version of the <a href="https://obis.org/dataset/47edd06e-212a-4677-9abb-6384a0cac20a">Atlantic and Gulf Rapid Reef Assessment — Benthic</a> dataset from OBIS which is already uploaded into ODP. Check my previous <a href="https://medium.com/@adrevant/introduction-to-the-ocean-data-platform-496736703355">blog post</a> for more context.</p><p>Before we get started, make sure you have an active OpenAI API key. If you don’t, <a href="https://www.youtube.com/watch?v=dJ5aIRUyhNA">this tutorial</a> can help you get set up. Additionally, as a good practice you shouldn’t store your credentials in raw text or within your code. Instead, use environment variables or a secrets manager to keep them secure and prevent accidental exposure, especially when sharing or publishing your code. This helps protect your account from unauthorized access and potential misuse of your API quota.</p><p>I use <a href="https://dotenvx.com/">dotenvx</a> to manage environment variables since this is a local project. To add an extra layer of security, I also encrypt my .env file, ensuring that sensitive data like API keys remain protected even if the file is accidentally exposed.</p><p>Quick tutorial:</p><pre># paste your creds in your .env file<br># example: OPENAI_API_KEY=abc123<br>nano .env</pre><pre># encrypt the contents of your .env file<br># the -f flag specifies the file to encrypt<br>dotenvx encrypt -f .env</pre><p>Expected output:</p><pre>✔ encrypted (.env)<br>✔ key added to .env.keys (DOTENV_PRIVATE_KEY)<br>⮕  next run [dotenvx ext gitignore --pattern .env.keys] to gitignore .env.keys<br>⮕  next run [DOTENV_PRIVATE_KEY=&#39;231b09[some more characters]&#39; dotenvx run -- yourcommand] to test decryption locally</pre><p>An .env.keys file will be created automatically when you encrypt your environment variables. This file contains the metadata required to manage encrypted environments, including key fingerprints and configuration details. While it doesn’t include any actual secrets, it is necessary for decrypting the encrypted environment, so be sure to keep it safe and include it in version control if you&#39;re working with others who share the decryption passphrase. As I am using jupyter notebooks for running my codes, this is how I start jupyter: DOTENV_PRIVATE_KEY=&#39;231b09[some more characters]&#39; dotenvx run -- jupyter lab OR dotenvx run -- jupyter lab -f .env , and in that way, dotenvx will inject my environment variables.</p><p>ODP now supports API secret keys, allowing users to securely authenticate and integrate with ODP services programmatically. In my case, I chose to authenticate using an API key for a more seamless, script-friendly workflow. Alternatively, if you don’t provide an API key, initializing Client() will trigger the ODP UI, where you can log in manually through your browser.</p><pre># get credentials<br>open_api_key = os.getenv(&quot;openai_api_key&quot;)<br>hub_ocean_key = os.getenv(&quot;hub_ocean_key&quot;)</pre><pre>cli = Client(api_key = hub_ocean_key)</pre><p>Check out this gif to get your dataset uuid:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*m4RcAaUGFepKtqjf45fLGQ.gif" /><figcaption>Get your dataset uuid</figcaption></figure><p>I am requesting two datasets: the curated coral reef dataset, and a Hub Ocean–curated dataset from Marine Regions containing the list of the 10 main oceans and seas (Arctic Ocean, North and South Atlantic Ocean, North and South Pacific Ocean, etc.), to identify in which ocean the coral reefs observed are located based on their coordinates.</p><pre># get existing datasets<br>reef_communities_dataset = cli.dataset(&quot;2d98ff7a-1af4-4eab-a0c9-f8817e49e989&quot;)<br><br># extracting main ocean from Hub Ocean curated dataset<br>marine_regions_oceans_dataset = cli.dataset(&quot;8a0aee54-879a-4223-a23e-5e60e96cf643&quot;)<br><br>reef_communities = reef_communities_dataset.select(<br>        #cols = [&quot;valid_scientific_name&quot;, &quot;latitude&quot;, &quot;longitude&quot;]<br>    ).all().dataframe() # to pandas df<br>oceans_polygons = marine_regions_oceans_dataset.select().rows()</pre><h4>Locating Coral Reefs in the Ocean</h4><p>To make my workflow smoother, I wrote a few helper functions that allow me to efficiently identify marine context based on geometry-based filtering, coordinate lookup, and external data sources.</p><ul><li>First, I built a <strong>spatial index</strong> (build_spatial_index) of ocean polygons using bounding boxes and geometries. This lets me quickly check which ocean a given point might fall into without scanning through every polygon manually.</li><li>Then, with a lookup function (find_ocean), I can pass in any (lat, lon) pair and efficiently identify <strong>which ocean</strong> contains that point. The function first narrows down potential candidates using the spatial index, and then either checks against bounding boxes (fast, but approximate) or full geometries (slower, but accurate).</li><li>On top of that, I connected to the <strong>Marine Regions API</strong> to enrich the results. Depending on the request type, the API can tell me the water body (e.g., Caribbean Sea) associated with the coordinates, or return lists of special regions such as World Marine Heritage Sites or Historical Fishing Areas.</li></ul><p>In short, these helpers allow me to go from raw coordinates → ocean → specific water body → heritage or protected status, which makes it much easier to contextualize and analyze marine data.</p><pre>def build_spatial_index(oceans_polygons, coords_names, contains_geometry = False):<br>    <br>    idx = index.Index()<br>    geometries = []<br>    <br>    # unpack coordinate field names from the provided dictionary<br>    min_X = coords_names[&quot;min_X&quot;]<br>    min_Y = coords_names[&quot;min_Y&quot;]<br>    max_X = coords_names[&quot;max_X&quot;]<br>    max_Y = coords_names[&quot;max_Y&quot;]<br><br>  # case 1: Each polygon contains a geometry (WKT string or multipolygon)<br>    if contains_geometry == True:<br>        for i, ocean in enumerate(oceans_polygons):<br>            try:<br>            <br>              # build bounding box (min_x, min_y, max_x, max_y)<br>                bbox = (<br>                    min(ocean[min_X], ocean[max_X]),<br>                    min(ocean[min_Y], ocean[max_Y]),<br>                    max(ocean[min_X], ocean[max_X]),<br>                    max(ocean[min_Y], ocean[max_Y])<br>                )<br>                <br>                # insert bounding box into the R-tree with index<br>                idx.insert(i, bbox)<br>                <br>                # store parsed geometry object and name for later use<br>                geometries.append({<br>                    &quot;geometry&quot;: wkt.loads(ocean[&quot;geometry&quot;]),<br>                    &quot;name&quot;: ocean[&quot;name&quot;]<br>                })<br>            except Exception as e:<br>                print(f&quot;Skipping ocean {i} due to error: {e}&quot;)<br>                continue<br>    <br>        return idx, geometries<br><br>    else:<br>      # case 2: Only bounding boxes are provided, no explicit geometry<br>        for i, feature in enumerate(oceans_polygons):<br>            try:<br>              # build bounding box<br>                bbox = (<br>                    feature[min_Y],<br>                    feature[min_X],<br>                    feature[max_Y],<br>                    feature[max_X]<br>                )<br><br>                # insert bounding box into the R-tree<br>                idx.insert(i, bbox)<br>                <br>                # store bbox and name (fallback to &quot;Unnamed_i&quot; if missing)<br>                geometries.append({<br>                    &quot;bbox&quot;: bbox,<br>                    &quot;name&quot;: feature.get(&quot;preferredGazetteerName&quot;, f&quot;Unnamed_{i}&quot;)<br>                })<br>                <br>            except Exception as e:<br>                print(f&quot;Skipping ocean {i} due to error: {e}&quot;)<br>                continue<br>    <br>        return idx, geometries</pre><pre>def find_ocean(lat, lon, spatial_index, geometries, search_type = None):<br>    <br>    point = Point(lon, lat)<br>    <br>    # Query the R-tree index to get candidate polygons that might intersect with the point.<br>    # Here we use a &quot;degenerate box&quot; (lon, lat, lon, lat) that represents a single point.<br>    candidates = list(spatial_index.intersection((lon, lat, lon, lat)))<br>    <br>    # fast bounding box containment check<br>    if search_type == &quot;bbox&quot;:<br>        for idx in candidates:<br>          # extract the bounding box (min_x, min_y, max_x, max_y)<br>            min_x, min_y, max_x, max_y = geometries[idx][&quot;bbox&quot;]<br>            <br>            # check if the point falls inside the bounding box<br>            if min_x &lt;= lon &lt;= max_x and min_y &lt;= lat &lt;= max_y:<br>                return geometries[idx][&quot;name&quot;]<br><br>  # full geometry containment check (slower but accurate)<br>    else:<br>        for idx in candidates:<br>          # test whether the polygon geometry actually contains the point<br>            if geometries[idx][&quot;geometry&quot;].contains(point):<br>                return geometries[idx][&quot;name&quot;]<br><br>    return &quot;unknown&quot;</pre><pre>def get_water_body(req_type, lat = None, lon = None):<br>    <br>    request_types = {<br>        1: &quot;coordinates&quot;,<br>        2: &quot;seavox seaarea - sub-region&quot;, <br>        3: &quot;World Marine Heritage Site&quot;,<br>        4: &quot;Historical fishing areas&quot;,<br>    }<br>    request_type = request_types[req_type]<br><br>    params = {&quot;offset&quot;: 0}<br>    headers = {&quot;accept&quot;: &quot;application/json&quot;}<br>    <br>    # lookup by geographic coordinates<br>    if request_type == &quot;coordinates&quot;:<br>        #url = &quot;https://www.marineregions.org/rest/getGazetteerRecordsByLatLong.json/20.114166/-87.456390/?offset=0&quot;<br>        url = f&quot;https://www.marineregions.org/rest/getGazetteerRecordsByLatLong.json/{lat}/{lon}/&quot; # needs to end with &quot;/&quot;<br>        target_region = None<br>        response = requests.get(url, params = params, headers = headers)<br><br>        if response.status_code == 200:<br>            data = response.json()<br>            if not data:<br>                return target_region<br><br>      # take the first result&#39;s preferred name<br>            target_region = data[0][&quot;preferredGazetteerName&quot;]<br>            return target_region<br>            <br>        else:<br>            print(&quot;API error&quot;)<br>            return response<br><br>  # lookup by region type (sub-region, heritage site, fishing area, etc.)<br>    else:<br>        # e.g. the Norwegian Sea is a sub-region within the broader Arctic Ocean<br>        url = f&quot;https://www.marineregions.org/rest/getGazetteerRecordsByType.json/{request_type}/&quot;<br>        response = requests.get(url, params = params, headers = headers)<br>        if response.status_code == 200:<br>            data = response.json()<br>            return data<br><br>        else:<br>            print(&quot;API error&quot;)<br>            return response</pre><p>In the following blocks of code you’ll find how I implemented the helper functions by creating spatial indexes for different types of marine regions. Each index makes it fast to look up which region a given point belongs to, as it avoids scanning through every polygon one by one.</p><ul><li><strong>Oceans</strong>: I first built an index of the world’s ocean polygons. Since these polygons include full geometry information, I set contains_geometry=True in build_spatial_index. This gave me both an R-tree spatial index and a list of Shapely geometries that I could query later with coordinates.</li><li><strong>Sea subregions</strong>: Next, I used the Marine Regions API (get_water_body(2)) to retrieve <em>SeaVox sub-regions</em> (smaller seas contained within larger oceans). These records don’t include geometries, only bounding boxes, so I created another index with just their coordinate ranges.</li><li><strong>World Heritage Sites</strong>: Similarly, I pulled data for <em>World Marine Heritage Sites</em> (get_water_body(3)) and built yet another spatial index for them. With this, I can check whether a coordinate not only falls in an ocean or sea, but also overlaps with a protected or heritage-designated area.</li></ul><pre>%%time<br><br># ocean polygons indexes<br>coordinates_list_names = {<br>    &quot;min_X&quot;: &quot;min_X&quot;,<br>    &quot;min_Y&quot;: &quot;min_Y&quot;,<br>    &quot;max_X&quot;: &quot;max_X&quot;,<br>    &quot;max_Y&quot;: &quot;max_Y&quot;<br>}<br>spatial_index, geometries = build_spatial_index(oceans_polygons, coordinates_list_names, contains_geometry = True)</pre><pre># sea subregion indexes<br>coordinates_list_names = {<br>    &quot;min_X&quot;: &quot;minLatitude&quot;,<br>    &quot;min_Y&quot;: &quot;minLongitude&quot;,<br>    &quot;max_X&quot;: &quot;maxLatitude&quot;,<br>    &quot;max_Y&quot;: &quot;maxLongitude&quot;<br>}<br><br>sea_subregions = get_water_body(2)<br>subregions_spatial_index, subregions_geometries = build_spatial_index(sea_subregions, coordinates_list_names)</pre><pre># world heritage indexes<br>coordinates_list_names = {<br>    &quot;min_X&quot;: &quot;minLatitude&quot;,<br>    &quot;min_Y&quot;: &quot;minLongitude&quot;,<br>    &quot;max_X&quot;: &quot;maxLatitude&quot;,<br>    &quot;max_Y&quot;: &quot;maxLongitude&quot;<br>}<br><br>heritage_sites_subregions = get_water_body(3)<br>heritage_sites_spatial_index, heritage_sites_geometries = build_spatial_index(heritage_sites_subregions, coordinates_list_names)</pre><p>By the end of this step, I had three complementary indexes — oceans, sea subregions, and heritage sites that together allow me to take a raw (lat, lon) pair and return a rich description of its marine context.</p><p>Afterwards, I enriched my reef dataset by tagging each unique coordinate with its marine context. First, I identified the main ocean where each point is located. Then, I refined this by assigning a sea subregion (e.g., Caribbean Sea within the Atlantic). I also checked whether the location overlapped with a World Marine Heritage Site, adding a conservation layer of information. Finally, I merged these results back into the full dataset, so every reef record now carries details about its ocean, water body, and heritage status.</p><p>Note that I am only applying the functions to unique pairs of coordinates in order to avoid redundant lookups and speed up processing. Since multiple reef records can share the same latitude and longitude, running the spatial queries once per unique location ensures efficiency while still allowing me to merge the enriched results back into the full dataset.</p><pre># drop repeated coordinates<br>coordinates = reef_communities[[&quot;latitude&quot;, &quot;longitude&quot;]].drop_duplicates()<br><br># find main ocean<br>coordinates[&quot;ocean&quot;] = coordinates[[&quot;latitude&quot;, &quot;longitude&quot;]].progress_apply(<br>    lambda x: find_ocean(x.latitude, x.longitude, spatial_index, geometries),<br>    axis = 1<br>)<br><br># find sea subregion<br>coordinates[&quot;water_body&quot;] = coordinates[[&quot;latitude&quot;, &quot;longitude&quot;]].progress_apply(<br>    lambda x: find_ocean(x.latitude, x.longitude, subregions_spatial_index, subregions_geometries, &quot;bbox&quot;),<br>    axis = 1<br>)<br><br># find world heritage site (if applies)<br>coordinates[&quot;heritage_site&quot;] = coordinates[[&quot;latitude&quot;, &quot;longitude&quot;]].progress_apply(<br>    lambda x: find_ocean(x.latitude, x.longitude, heritage_sites_spatial_index, heritage_sites_geometries, &quot;bbox&quot;),<br>    axis = 1<br>)<br><br># string to wkt format<br>coordinates[&quot;wkt_coordinates&quot;] = coordinates.apply(<br>    lambda row: f&quot;POINT ({row[&#39;longitude&#39;]} {row[&#39;latitude&#39;]})&quot;, <br>    axis = 1<br>)<br><br>reef_communities = reef_communities.merge(coordinates, on = [&quot;latitude&quot;, &quot;longitude&quot;], how = &#39;left&#39;)<br>del coordinates, subregions_spatial_index, subregions_geometries<br>del spatial_index, geometries</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wgHYYB1HooHt_6Z4d5sYfw.png" /><figcaption>reef_communites dataframe after identifying marine locations</figcaption></figure><p>Let’s build a plot to explore the geographic distribution of coral reefs. Each circle on the map marks a reef community at its recorded latitude and longitude. The color shows which body of water the community belongs to, while the size of the circle reflects the number of individuals observed, so larger circles highlight areas with denser reef populations.</p><pre>fig = px.scatter_map(<br>    reef_communities,<br>    lat = &quot;latitude&quot;,<br>    lon = &quot;longitude&quot;,<br>    color = &quot;water_body&quot;,<br>    #color_continuous_scale = px.colors.cyclical.IceFire,<br>    color_discrete_sequence = [&quot;mediumpurple&quot;, &quot;mediumseagreen&quot;, &quot;#3260a5&quot;, &quot;peachpuff&quot;, &quot;cornflowerblue&quot;],<br>    size = &quot;individual_count&quot;,<br>    size_max = 15,<br>    zoom = 3,<br>    map_style = &quot;open-street-map&quot;,<br>    # control size of plot<br>    height = 700,<br>    width = 900<br>)<br><br>fig.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D_tUu9kGNX1tkqOxkg-fLw.png" /></figure><h4>Classifying Coral Types with AI</h4><p>Besides identifying different ocean bodies, I also used the OpenAI API to classify each coral type and determine whether it is a reef builder or not. This allowed me to pair the spatial context (ocean, sea subregion, heritage site) with biological information about the reef communities, giving a richer picture of where each coral is found and what species are present.</p><pre>def openai_corals_check(open_api_key, target_species, openai_request, format_request,<br>                        open_azure_endpoint = YOUR_ENDPOINT,<br>                        open_api_version = YOUR_VERSION,<br>                        model_name = YOUR_MODEL):<br><br>    client = AzureOpenAI(<br>        api_key = open_api_key,<br>        azure_endpoint = open_azure_endpoint,<br>        api_version = open_api_version,<br>    )<br><br>    # build the full prompt by appending the target species and any formatting instructions<br>    openai_request = openai_request + f&quot;{target_species}&quot; + format_request<br>    <br>    # send the prompt to the model and request a completion<br>    completion = client.chat.completions.create(<br>        model = model_name,<br>        messages = [<br>            {<br>                &quot;role&quot;: &quot;user&quot;,<br>                &quot;content&quot;: openai_request,<br>            },<br>        ],<br>        response_format = {&quot;type&quot;: &quot;json_object&quot;},<br>        temperature = 0.5, # controls the randomness of the generated text, ranging from 0 (deterministic) to 2 (more random)<br>    )<br>    <br>    return completion.choices[0].message.content</pre><p>Here’s the prompt that I used to identify a given list of coral species into coral types and if they are reef builders or not. Essentially, I’m feeding the species names into a prompt that asks the model to classify each one. The model responds with a structured JSON object that I can easily parse and use in my code.</p><pre>openai_request = &quot;&quot;&quot;<br>                    Based on this list of marine species names: <br>                &quot;&quot;&quot; <br>format_request = &quot;&quot;&quot;<br><br>                    Classify them into scleractinian coral, stony coral, alcyonacea, ahermatypic coral,<br>                    fire coral, solitary coral, fungiidae (mushroom coral), black coral,<br>                    deep-water coral, or any other suitable type. In addition, include a flag that tells<br>                    whether a coral specie is reef building. Please, provide the response in json<br>                    format in the form:<br><br>                    {<br>                        coral_types: {<br>                            specie_name_in_dataset: (coral_type, 1 or 0)<br>                        }<br>                    }<br><br>                &quot;&quot;&quot;</pre><p>While doing other tests I ran into a character limit issue with the OpenAI API. If the list of coral species is too large (&gt; 325), in some cases returning a single dictionary with all species and their corresponding tuples of (coral_type, reef_builder_flag) might exceed the model’s response size and cause the generated JSON/dictionary to be incomplete. To work around this, I had to split the list of species into smaller batches, sending multiple requests to ensure each response stayed within the API’s limits while still preserving the structured output. If the list of corals is ≤ 150 items, then a single batch should be ran, otherwise the list will be partitioned into multiple batches to overcome the character limit.</p><p>In addition, I ran the request for the same list 3 times so I can compare if the API responses are consistent.</p><pre>jumps = 150<br>target_api_runs = 3<br>coral_types_comparisson = []<br>target_species = reef_communities[&quot;valid_scientific_name&quot;].unique().tolist()<br><br># run the same request 3 times to compare results<br>for _ in range(target_api_runs):<br>    openai_corals = {}<br>    non_coral_species_per_run = []<br><br>    # check by batches because the API has a character limit<br>    for idx in range(0, len(target_species), jumps):<br>        partitioned_target_species = target_species[idx:idx+jumps]<br>        <br>        openai_corals_str = openai_corals_check(<br>            open_api_key = open_api_key,<br>            <br>            # make sure not to compare already identified species<br>            target_species = partitioned_target_species,<br>            openai_request = openai_request,<br>            format_request = format_request,<br>        )<br><br>        openai_coral_request = json.loads(openai_corals_str)<br><br>        # collect coral types<br>        openai_corals = {**openai_coral_request[&quot;coral_types&quot;], **openai_corals}<br><br>    # save results from differen API runs<br>    coral_types_comparisson.append(openai_corals)<br><br># intersection between all runs<br>coral_species_types = list(<br>    set(coral_types_comparisson[0]) &amp; set(coral_types_comparisson[1]) &amp; set(coral_types_comparisson[2])<br>)</pre><p>The following piece of code compares the results of all API runs to find those species that were identified with different coral types or reef building flags across runs.</p><pre># track all [type, reef_building_flag] values per species<br>coral_tracker = defaultdict(set)<br><br>for coral_dict in coral_types_comparisson:  # api_runs = your list of dicts<br>    for species, value in coral_dict.items():<br>        coral_tracker[species].add(tuple(value))  # Convert list to tuple to make it hashable<br><br># identify inconsistent species<br>conflicts = {species: values for species, values in coral_tracker.items() if len(values) &gt; 1}<br><br># display inconsistent entries<br>if conflicts:<br>    print(&quot;❌ Inconsistent species found across API runs:&quot;)<br>    for species, entries in conflicts.items():<br>        print(f&quot;  {species}: {entries}&quot;)<br>else:<br>    print(&quot;✅ All species are consistent across API runs.&quot;)<br><br># remove inconsistent species only from the first run<br>filtered_first_run = {<br>    k: v for k, v in coral_types_comparisson[0].items() if k not in conflicts<br>}<br><br>filtered_first_run = {<br>    k: {<br>        &quot;type&quot;: v[0],<br>        &quot;is_reef_building&quot;: v[1]<br>    }<br>    <br>    for k,v in filtered_first_run.items()<br>}<br><br>print(f&quot;{len(filtered_first_run)} consistent species retained.&quot;)<br>print(f&quot;{len(conflicts)} inconsistent species removed.&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*NYfCfFjuIAiYhT-x9b9Pbw.png" /><figcaption>Inconsistent coral classification list</figcaption></figure><p>Next, I added the results of species with no conflicts across requests.</p><pre># add specie type and whether is reef building to the dataset<br>reef_communities[&quot;species_type&quot;] = reef_communities[&quot;valid_scientific_name&quot;].apply(<br>    lambda name: filtered_first_run.get(name, {}).get(&quot;type&quot;)<br>)<br><br>reef_communities[&quot;is_reef_building&quot;] = reef_communities[&quot;valid_scientific_name&quot;].map(<br>    lambda name: filtered_first_run.get(name, {}).get(&quot;is_reef_building&quot;)<br>)<br><br># keep null vaslues<br>reef_communities[&quot;is_reef_building&quot;] = reef_communities[&quot;is_reef_building&quot;].astype(str)<br>replacements = {&#39;1.0&#39;: &#39;true&#39;, &#39;0.0&#39;: &#39;false&#39;, &#39;nan&#39;:&#39;unknown&#39;}<br>reef_communities = reef_communities.replace(replacements)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*EV6yR4KJSicgZaqU1_LUcQ.png" /><figcaption>reef_communities dataset after corals classification</figcaption></figure><p>I decided to create an interactive map to visualize the locations of reef communities. Just as in the last map, each circle you see represents a reef community at its recorded latitude and longitude.</p><pre>gdf = gpd.GeoDataFrame(<br>    reef_communities,<br>    geometry=gpd.points_from_xy(reef_communities[&quot;longitude&quot;], reef_communities[&quot;latitude&quot;]),<br>    crs=&quot;EPSG:4326&quot;,<br>    #crs = &quot;EPSG:3857&quot;,<br>)<br><br># sample down for interactivity performance<br>sample_n = 5000<br>if len(gdf) &gt; sample_n:<br>    rc_fol = gdf.sample(sample_n, random_state=42)<br>else:<br>    rc_fol = gdf<br><br>m = folium.Map(location=[0, 0], zoom_start=2, tiles=&quot;cartodbpositron&quot;)<br>marker_cluster = MarkerCluster().add_to(m)<br><br><br>def _popup(row):<br>    ocean = row.get(&quot;ocean&quot;, &quot;?&quot;)<br>    wb = row.get(&quot;water_body&quot;, &quot;?&quot;)<br>    sp = row.get(&quot;species_type&quot;, &quot;?&quot;)<br>    rb = row.get(&quot;is_reef_building&quot;, &quot;?&quot;)<br>    return f&quot;&lt;b&gt;Ocean:&lt;/b&gt; {ocean}&lt;br&gt;&lt;b&gt;Water body:&lt;/b&gt; {wb}&lt;br&gt;&lt;b&gt;Species type:&lt;/b&gt; {sp}&lt;br&gt;&lt;b&gt;Reef-building:&lt;/b&gt; {rb}&quot;<br><br>for _, r in rc_fol.iterrows():<br>    folium.CircleMarker(<br>        location=[r.geometry.y, r.geometry.x],<br>        radius=3,<br>        fill=True,<br>        popup=folium.Popup(_popup(r), max_width=300)<br>    ).add_to(marker_cluster)<br><br>out_html = Path(&quot;reef_map_interactive.html&quot;)<br>m.save(out_html)<br>#print(f&quot;Saved interactive map to {out_html.resolve()} – open in a browser.&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*C0cC9YsXLTczn48vcQqA0w.gif" /><figcaption>Interactive map of coral reefs geographic distribution</figcaption></figure><p>Find <a href="https://github.com/adrevant/medium-lab/blob/corals_classification/hub_ocean_tests/blog_images/b2/reef_map_interactive.html">here</a> my interactive map!</p><h3>Updating dataset in ODP</h3><p>Now that I have enriched my dataset with new information (columns), I had to update my dataset schema in ODP in order to ingest the new data with no conflicts.</p><p>In this case, you’ll have to declare the old schema + add the new columns. In other words, you have to add all columns and use the table.alter() method.</p><pre># update table schema<br>reef_communities_dataset.table.alter(<br>    pa.schema([<br>        ...,<br>        ...,<br>        pa.field(&quot;individual_count&quot;, pa.int64(),<br>                 metadata = {&quot;description&quot;: &quot;Count of individuals observed&quot;}),<br>        pa.field(&quot;country&quot;, pa.string(),<br>                 metadata = {&quot;description&quot;: &quot;Country where the observation was made&quot;}),<br>        pa.field(&quot;ocean&quot;, pa.string(), nullable = True,<br>                 metadata = {&quot;description&quot;: &quot;Name of the ocean where lat,lon coordinates fall&quot;}),<br>        pa.field(&quot;water_body&quot;, pa.string(),<br>                 metadata = {&quot;description&quot;: &quot;Name of the sea subregion where lat,lon coordinates fall&quot;}),<br>        pa.field(&quot;heritage_site&quot;, pa.string(),<br>                 metadata = {&quot;description&quot;: &quot;Name of the World Marine Heritage Site where lat,lon coordinates fall (if applies)&quot;}),<br>        pa.field(&quot;species_type&quot;, pa.string(),<br>                 metadata = {&quot;description&quot;: &quot;Name of the coral specie type&quot;}),<br>        #pa.field(&quot;is_reef_building&quot;, pa.bool_(), nullable = True)<br>        pa.field(&quot;is_reef_building&quot;, pa.string(), nullable = True,<br>                 metadata = {&quot;description&quot;: &quot;Flag that identifies if the coral type is reef-builder or not&quot;}),<br>        pa.field(&quot;wkt_coordinates&quot;, pa.string(),<br>                 metadata = {&quot;description&quot;: &quot;Coordinates of the observation in WKT format&quot;,<br>                            &quot;isGeometry&quot;: &quot;1&quot;})<br>    ])<br>)</pre><p>To ensure consistency and avoid partial updates, I decided to fully replace the original dataset with the contents of my reef_communities dataframe. This dataframe includes both the original records that I retrieved from ODP and the newly added columns that enrich the data. By performing a complete overwrite, I eliminate the risk of mismatches or incomplete updates across records.</p><p>If you prefer to update records row-by-row rather than using the drop + insert approach, you can refer to the code provided in the repository linked above where I added an example. However, this is not the recommended method.</p><p>ODP is built on <strong>immutable columnar storage</strong>, which is optimized for <strong>fast reads</strong> and <strong>efficient querying</strong>, not for in-place updates. It also leverages dynamic partitioning to speed up certain types of queries. Because of this architecture, updating data typically involves replacing entire records or batches, rather than modifying individual rows (my tests took nearly 3 hours to complete the full dataset update).</p><pre>%%time<br>with reef_communities_dataset as tx:<br>  tx.delete(&quot;1=1&quot;)  # drop the data, keep the schema<br>  tx.insert(reef_communities)</pre><p>ODP datasets can now be shared publicly via direct links, even without being published in the official ODP public catalog. This makes it easy to informally share datasets with collaborators while still maintaining full access to the data. If formal publication is desired (e.g., listing in the public catalog), the dataset must meet ODP’s data quality and compliance standards, and a review request must be submitted to the HUBOcean team. In the meantime, you can access the Reef Communities dataset directly <a href="https://app.hubocean.earth/catalog/dataset/54e04d81-fac3-447f-910c-ddedc8717666/atlantic-and-gulf-rapid-reef-assessment-benthic?">here</a>.</p><blockquote><em>You’ll need to log into your ODP account to view the ‘Tabular Data’ section.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HSDGYBVbAkSL2xesUMdcew.png" /></figure><p>A cool feature of the Ocean Data Platform (ODP) is that you can interact directly with data points when their locations are stored in WKT (Well-Known Text) format. In my dataset, I created a new column called wkt_coordinates that transforms the latitude and longitude values into valid WKT strings. This way, each reef observation isn’t just a pair of numbers but a spatial object that the platform can recognize and visualize on a map (in the schema see how I had to set &quot;isGeometry&quot;:&quot;1&quot;).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_eOTosiLeEVJPhF9kjZKrg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OUUWFUb9K4qDYMWGWjVteA.png" /><figcaption>Visualization of coral reef location in ODP</figcaption></figure><p>Working on this project gave me a much better understanding of how powerful it can be to combine different tools, like Hub Ocean’s Ocean Data Platform and OpenAI’s API, to explore and make sense of ocean data. By uploading and enriching a coral reef dataset, I was able to figure out things like which water body each reef was located in, whether the area was protected, and even what kind of coral species were present. It was especially cool to see how I could automate species classification with AI, instead of trying to manually research every name (which would’ve taken forever).</p><p>I also learned a lot about how ODP handles data behind the scenes like how it’s optimized for fast reads, why it uses immutable storage, and how to properly update datasets without running into trouble.</p><p>All in all, this project showed me that you don’t need to be an marine expert to start doing meaningful ocean research. With the right tools and a bit of curiosity, you can build your own workflows, ask better questions, and maybe even help protect these amazing ecosystems along the way.</p><p>If you’re curious about the ocean and want to dig into real data, I highly recommend giving <a href="https://www.hubocean.earth/platform">Hub Ocean ODP</a> a try. It’s free to join, and you can start exploring or uploading your own datasets in just a few clicks.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=27daf80b46c4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introduction to the Ocean Data Platform]]></title>
            <link>https://medium.com/@adrevant/introduction-to-the-ocean-data-platform-496736703355?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/496736703355</guid>
            <category><![CDATA[ocean-data]]></category>
            <category><![CDATA[bluetech]]></category>
            <category><![CDATA[sustainability]]></category>
            <category><![CDATA[marine-science]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Fri, 06 Jun 2025 04:43:11 GMT</pubDate>
            <atom:updated>2025-07-20T17:02:02.097Z</atom:updated>
            <content:encoded><![CDATA[<h4>Changing the fate of the ocean</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*esUhP6eXISHPzqmldEDsmw.jpeg" /></figure><blockquote><em>The complete code for this tutorial, along with detailed documentation of the functions, can be found in t</em><a href="https://github.com/ozeanith/medium-lab"><em>his github repository</em></a><em> at </em><em>hub_ocean_tests/</em><em>obis_worms_1.ipynb.</em></blockquote><p>In this post, we will explore the details of Hub Ocean, its impact, and how to load sample data into the Ocean Data Platform.</p><h3>Introduction</h3><p><a href="https://www.hubocean.earth/">Hub Ocean</a> is a non-profit foundation that aims to unlock the value of ocean data and provide equitable, open access to all users through its innovative Ocean Data Platform. This platform serves as a bridge between scattered and heterogeneous ocean data sources and the people who need access and insights from this information.</p><p>You can think of it as the <em>Spotify</em> of ocean data. Their focus is on bringing diverse “albums” of marine information into a high-powered streaming ecosystem. Instead of creating playlists scientists, companies, and ocean enthusiasts can create data collections (like coral reef data, shipping data, biodiversity records).</p><p>At the heart of it all is the Ocean Data Platform (ODP), their Eras Tour. The Ocean Data Platform connects isolated marine data into one seamless, powerful journey. From historic research and corporate records to government environmental observations and academic datasets. It also welcomes contributions from users themselves, whether the data is private or open-access. Every “era” of ocean exploration has its spotlight moment, and each dataset plays like a number in the setlist: valuable on its own, but even more powerful as part of the full performance.</p><p>The platform is designed to solve several critical challenges in ocean data management: ocean data stored in multiple sources and formats, difficulty in accessing and analyzing large-scale ocean datasets, need for standardized data processing and quality control, or limited collaboration opportunities between researchers and organizations.</p><h4>Why democratizing ocean data matters?</h4><p>Democratizing access to ocean data is crucial for several reasons:</p><ul><li>Scientific research: Enables researchers worldwide to access comprehensive datasets for marine studies</li><li>Climate action: Supports better understanding of ocean health, overfishing and climate change impacts</li><li>Policy making: Helps inform evidence-based marine policy and conservation efforts</li><li>Innovation: Fosters development of new solutions for ocean sustainability</li><li>Public awareness: Raise public awareness of ocean threats and encourage the general public to take actions to protects the seas.</li></ul><p>By making ocean data more accessible, we can accelerate scientific discoveries and innovation in marine research, improve our understanding of ocean ecosystems and their changes, enable better decision-making for ocean resource management, and foster collaboration between different stakeholders in the ocean science community, from public to private sectors.</p><h3>Exploring our data</h3><p>Now, let’s explore how to upload data to ODP using Python. For this example I will use the <a href="https://obis.org/dataset/47edd06e-212a-4677-9abb-6384a0cac20a">Atlantic and Gulf Rapid Reef Assessment — Benthic</a> dataset from OBIS, a global open-access database that provides data about marine species distributions and abundance.</p><h4>Data preprocessing</h4><pre># reading obis data and do basic cleaning<br>data = pd.read_csv(&#39;dwca-agrra-benthic-v1/occurrence.txt&#39;, sep = &#39;\t&#39;, engine = &#39;python&#39;)<br><br>data.columns = [re.sub(r&#39;(?&lt;!^)(?=[A-Z][a-z])&#39;, &#39;_&#39;, col).lower() for col in data.columns]<br><br>data = data.rename(columns = {&quot;decimal_latitude&quot;: &quot;latitude&quot;, &quot;decimal_longitude&quot;: &quot;longitude&quot;,<br>                              &quot;locationid&quot;: &quot;location_id&quot;})</pre><p>With data.info() we can get a basic summary of data completeness. Most columns are completely empty (23/40) including country, sex, life_stage and individual_count that could be interesting to work with. However, there is other information that could help us to enrich the dataset.</p><p>As this is a basic example of data ingestion into the platform, empty columns shouldn’t be a problem at this stage.</p><pre>data.info()<br><br># ------ Output sample ------ #<br>&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;<br>RangeIndex: 66248 entries, 0 to 66247<br>Data columns (total 41 columns):<br> #   Column                      Non-Null Count  Dtype  <br>---  ------                      --------------  -----  <br> 0   id                          66248 non-null  int64  <br> 1   bibliographic_citation      66248 non-null  object <br> 2   institution_code            66248 non-null  object <br> 3   collection_code             66248 non-null  object <br> 4   basis_of_record             66248 non-null  object <br> 5   dynamic_properties          0 non-null      float64<br> 6   occurrenceid                66248 non-null  int64  <br> 7   catalog_number              66248 non-null  int64  <br> 8   occurrence_remarks          0 non-null      float64<br> 9   record_number               0 non-null      float64<br> 10  recorded_by                 0 non-null      float64<br> 11  individual_count            0 non-null      float64<br> 12  sex                         0 non-null      float64<br> 13  life_stage                  0 non-null      float64<br>...  ...                         ...             ...<br>...  ...                         ...             ...<br>...  ...                         ...             ...<br>dtypes: float64(27), int64(6), object(8)<br>memory usage: 20.7+ MB</pre><p>I performed several cleanups on the species’ scientific names to handle common formatting variations like species names ending in “sp.” or “.sp&quot;, names followed by “X” or multiple “X” characters, names with excessive whitespace, or names with descriptions in parenthesis → scientific_name (some other description).</p><p>Through working with multiple OBIS datasets, I discovered that while they often share similar column structures, this isn’t always consistent. That&#39;s why I used the data.columns.intersection() method to find common column names between two sets of columns. In my case, it identifies the overlap between my target_columns and the dataframe’s actual columns. However, even when columns share names, their format or content can vary, as seen with event_date and individual_count, which are sometimes empty or with a different format.</p><pre>data[&#39;scientific_name&#39;] = data[&#39;scientific_name&#39;].apply(<br>    lambda x: re.sub(r&#39;\b(?:sp\.|\.sp)(?=\W|$)&#39;, &#39;&#39;, x.lower()).strip()<br>)<br><br># Name X | Name XX{3,4, ...} -&gt; Name<br>data[&#39;scientific_name&#39;] = data[&#39;scientific_name&#39;].apply(<br>    lambda x: re.sub(r&#39;\s+x+$&#39;, &#39;&#39;, x.strip(), flags=re.IGNORECASE)<br>)<br><br># remove parenthesis from names<br># e.g. porites colonensis (sensu p. humann) --&gt; porites colonensis<br>data[&#39;scientific_name&#39;] = data[&#39;scientific_name&#39;].str.split(&#39;(&#39;).str[0].str.strip()<br><br>if &quot;event_date&quot; in data.columns:<br>    data[&quot;event_date&quot;] = data[&quot;event_date&quot;].apply(<br>        lambda x: x + &quot;-01-01&quot; if len(str(x)) == 4 else (x + &quot;-01&quot; if len(str(x)) == 7 else x)<br>    )<br>    data[&#39;event_date&#39;] = pd.to_datetime(data[&#39;event_date&#39;])<br><br>if &quot;individual_count&quot; in data.columns:<br>    # just integers<br>    data[&#39;individual_count&#39;] = data[&#39;individual_count&#39;].fillna(0).astype(int)<br><br>target_cols = [&quot;scientific_name&quot;, &quot;valid_scientific_name&quot;, &quot;matching_type&quot;, &quot;unaccept_reason&quot;,<br>               &quot;rank&quot;, &quot;individual_count&quot;, &quot;event_date&quot;, &quot;minimum_depth_in_meters&quot;,<br>               &quot;maximum_depth_in_meters&quot;, &quot;latitude&quot;, &quot;longitude&quot;, &quot;country&quot;]<br><br>data = data[data.columns.intersection(target_cols)].reset_index(drop = True)</pre><h4>Data enrichment</h4><p>Since the individual_countcolumn is empty, we’ll populate it by calculating the number of observations for each unique combination of latitude, longitude, and species name. This approach helps us estimate population density at each sampling location.</p><pre>data_individual_counts = data[data[&#39;individual_count&#39;] == 0]\<br>    .groupby([&quot;latitude&quot;, &quot;longitude&quot;, &quot;scientific_name&quot;])\<br>    .size()\<br>    .reset_index(name = &quot;calculated_individual_count&quot;)<br><br>data = data.merge(<br>    data_individual_counts,<br>    on = [&quot;latitude&quot;, &quot;longitude&quot;, &quot;scientific_name&quot;],<br>    how = &#39;left&#39;<br>)<br><br># fill original column<br>data[&#39;individual_count&#39;] = np.where(<br>    data[&#39;individual_count&#39;].isnull() | (data[&#39;individual_count&#39;] == 0),<br>    data[&#39;calculated_individual_count&#39;],<br>    data[&#39;individual_count&#39;]<br>)<br><br>del data_individual_counts</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IwhvbzxhuRkFhnwzYJt8SQ.png" /><figcaption>data_individual_counts dataframe</figcaption></figure><p>The country column also contains no data, but we can leverage the existing latitude and longitude coordinates to determine country locations using <strong>Nominatim’s</strong> reverse geocoding capabilities.</p><pre>def custom_locator(lat, lon):<br>    &quot;&quot;&quot;<br>    Returns the country name for a given pair of latitude and longitude.<br>    &quot;&quot;&quot;<br>    lat = str(lat)<br>    lon = str(lon)<br>    <br>    # initialize Nominatim API <br>    geolocator = Nominatim(user_agent=&quot;hub_ocean_geopy_app&quot;, timeout = 10)<br>    geocode = RateLimiter(geolocator.reverse, min_delay_seconds=1)<br><br>    location = geocode(f&quot;{lat},{lon}&quot;, language=&quot;en&quot;)<br><br>    try:<br>        address = location.raw[&#39;address&#39;]<br>        return address.get(&#39;country&#39;, &#39;unknown&#39;)<br><br>    except:<br>        return &quot;unknown&quot;</pre><p>As you may have seen from the quick dataset summary above, there are 66,248 entries, but only 731 unique pairs of (latitude, longitude). That&#39;s why I decided to drop duplicated records based on those two columns, and only apply the function above to unique coordinates. Moreover, observe that I&#39;m using progress_apply, a function by tqdm to provide progress on Pandas’ apply function.</p><p>Nominatim works pretty well for reverse geocoding, but to extract the country of each coordinate takes 12-15 min (measured with progress_apply).</p><pre># keep records where &quot;country&quot; is null and drop repeated coordinates<br>coordinates = data[data[&#39;country&#39;].isnull()][[&quot;latitude&quot;, &quot;longitude&quot;]].drop_duplicates()<br><br>coordinates[&quot;country_coordinates&quot;] = coordinates.progress_apply(<br>        lambda x: custom_locator(x.latitude, x.longitude), axis = 1<br>)<br><br>data = data.merge(coordinates, on = [&quot;latitude&quot;, &quot;longitude&quot;], how = &#39;left&#39;)<br>del coordinates<br><br># fill original column<br>data[&#39;country&#39;] = np.where(data[&#39;country&#39;].isnull(), data[&#39;country_coordinates&#39;], data[&#39;country&#39;])<br>data.head(3)</pre><p>I also decided to validate the species names against the World Register of Marine Species (WoRMS) to ensure data accuracy and standardization. WoRMS serves as the authoritative global reference for marine species nomenclature.</p><p>This validation step is crucial for several reasons:</p><ul><li>WoRMS maintains an up-to-date taxonomy, tracking changes in species names over time</li><li>It helps identify and correct potential misspellings, synonyms, or outdated nomenclature in the dataset</li><li>The validation process provides additional taxonomic information like rank and acceptance status</li><li>Using standardized names makes our data more interoperable with other marine datasets</li></ul><p>To accomplish this, I implemented a function that interacts with the WoRMS API to check and validate each species name in our dataset.</p><pre>def get_worms_accepted_names(scientific_names, like = &quot;true&quot;,<br>                             marine_only = &quot;true&quot;, extant_only = &quot;false&quot;):<br>    &quot;&quot;&quot;<br>    Compares a list of marine species names with WoRMS records using the WoRMS API<br>    to check if the names are valid.<br>    &quot;&quot;&quot;<br><br>    # build the query parameters with repeated &#39;scientificnames[]&#39;<br>    # the [ ] in scientificnames[] matters a lot to the WoRMS API<br>    # because it&#39;s built with PHP-style parameter handling<br>    url = &quot;https://www.marinespecies.org/rest/AphiaRecordsByNames&quot;<br><br>    # cannot use dictionary here because it can only have one <br>    # value per key. If you write:<br>    # {&quot;scientificnames[]&quot;: &quot;A&quot;, &quot;scientificnames[]&quot;: &quot;B&quot;}<br>    # the second one overwrites the first<br>    params = [(&quot;scientificnames[]&quot;, name) for name in scientific_names]<br>    params += [<br>        (&quot;like&quot;, like),<br>        (&quot;marine_only&quot;, marine_only),<br>        (&quot;extant_only&quot;, extant_only)<br>    ]<br><br>    headers = {&quot;accept&quot;: &quot;application/json&quot;}<br>    response = requests.get(url, params = params, headers = headers)<br><br>    if response.status_code != 200:<br>        return f&quot;Error: {response.status_code}&quot;<br><br>    # [<br>    #    [{}, {}, {}], # specie 1<br>    #    [{}],         # specie 2<br>    #    [{}, {}]      # specie 3<br>    # ]<br>    return response.json()</pre><p>With the .unique() method, I removed duplicated species names to optimize API calls. This ensures we only query the WoRMS API once per specie, improving both efficiency and API usage.</p><pre># get list of unique speices names<br>scientific_names = data.scientific_name.unique().tolist()<br>results = get_worms_accepted_names(scientific_names)<br><br># generate list of dictionaries<br>worms_data_list = [{<br>    &quot;scientific_name&quot;: sp_name,<br>    &quot;valid_name&quot;: worms_response[0].get(&quot;valid_name&quot;) if worms_response else &quot;unknown&quot;,<br>    &quot;matching_type&quot;: worms_response[0].get(&quot;status&quot;) if worms_response else &quot;unknown&quot;,<br>    &quot;rank&quot;: worms_response[0].get(&quot;rank&quot;) if worms_response else &quot;unknown&quot;,<br>    &quot;unaccept_reason&quot;: worms_response[0].get(&quot;unacceptreason&quot;) if worms_response else &quot;unknown&quot;,<br>} for sp_name, worms_response in zip(scientific_names, results)]<br><br><br>worms_data = pd.DataFrame(worms_data_list)<br>worms_data[[&quot;valid_name&quot;, &quot;matching_type&quot;, &quot;rank&quot;]] = worms_data[[&quot;valid_name&quot;, &quot;matching_type&quot;, &quot;rank&quot;]]\<br>                                                        .apply(lambda col: col.str.lower())<br><br># keep original species names and WoRMS suggested names<br>data = data.merge(worms_data, on = &#39;scientific_name&#39;, how = &#39;left&#39;)<br>data = data.rename(columns = {&quot;valid_name&quot;: &quot;valid_scientific_name&quot;})<br>data = data[data.columns.intersection(target_cols)].reset_index(drop = True)<br>data[[&quot;valid_scientific_name&quot;, &quot;matching_type&quot;, &quot;rank&quot;]] = (<br>    data[[&quot;valid_scientific_name&quot;, &quot;matching_type&quot;, &quot;rank&quot;]]<br>    .fillna(&quot;unknown&quot;)<br>)<br><br>data[&#39;unaccept_reason&#39;] = data[&#39;unaccept_reason&#39;].fillna(&quot;None&quot;)</pre><p>Looking at the matching types from WoRMS, I found some interesting patterns. Most species names were marked as “accepted”, which means they’re correct and up-to-date. While a few names had spelling errors or weren’t accepted, what’s particularly interesting are the superseded combination and junior subjective synonym cases.</p><pre>data[&#39;matching_type&#39;].value_counts()<br><br># ------ Output ------ #<br>matching_type<br>accepted                                       44484<br>superseded combination                         21254<br>misspelling - incorrect subsequent spelling      342<br>junior subjective synonym                        118<br>unknown                                           44<br>unaccepted                                         6<br>Name: count, dtype: int64</pre><p>These two taxonomic terms often show up when looking at species data in systems like WoRMS or GBIF. They both relate to naming history and how taxonomists have updated classifications, but they mean different things.</p><p>A superseded combination happens when the <strong>species stays the same</strong>, but the <strong>genus changes</strong> (basically a reclassification, not a synonym). This is important when matching historical data or publications that use older combinations. On the other hand, <strong>junior subjective synonym </strong>means that two different names were proposed for what is now believed to be the <strong>same species</strong> but the decision is based on <strong>expert opinion</strong>, not hard rules.</p><ul><li>Superseded combination example:<br>- Original: <em>Squalus albacares<br>- </em>Current (accepted): <em>Thunnus albacares</em><br>- <em>albacares</em> is the same species, but it got <strong>moved from one genus to another</strong>. Therefore, “superseded combination” refers to an old name format that was replaced by a new genus-species combination.</li><li>Junior subjective synonym example:<br>- <em>Thunnus albacares</em> (proposed earlier)<br>- <em>Thunnus pelamys</em> (proposed later)<br>- If both were thought to describe the same species, then <em>pelamys</em> becomes a <strong>junior subjective synonym</strong> of <em>albacares</em>, which has priority. This kind of synonymy is <strong>not due to genetic or rule-based identity</strong>, but rather taxonomists’ consensus based on morphology, behavior, distribution, etc.</li></ul><p>Quick summary:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4853833dc1b74a70ba0743fb86685171/href">https://medium.com/media/4853833dc1b74a70ba0743fb86685171/href</a></iframe><p>Going back to our data, by running data.info()once again we can see that we only have the target columns and nearly zero null values in all of them. We are now ready to upload our data!</p><pre>&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;<br>RangeIndex: 66248 entries, 0 to 66247<br>Data columns (total 12 columns):<br> #   Column                   Non-Null Count  Dtype              <br>---  ------                   --------------  -----              <br> 0   individual_count         66248 non-null  int64              <br> 1   event_date               66248 non-null  datetime64[ns, UTC]<br> 2   country                  66248 non-null  object             <br> 3   minimum_depth_in_meters  64710 non-null  float64            <br> 4   maximum_depth_in_meters  64710 non-null  float64            <br> 5   latitude                 66248 non-null  float64            <br> 6   longitude                66248 non-null  float64            <br> 7   scientific_name          66248 non-null  object             <br> 8   valid_scientific_name    66248 non-null  object             <br> 9   matching_type            66248 non-null  object             <br> 10  rank                     66248 non-null  object             <br> 11  unaccept_reason          66248 non-null  object             <br>dtypes: datetime64[ns, UTC](1), float64(4), int64(1), object(6)<br>memory usage: 6.1+ MB</pre><h3>Ingesting into the Ocean Data Platform</h3><p>Data can be ingested into the platform via the web interface, the API, or using the SDK like in this example. And the best part? you don’t need to write a single line of code to get your data onboard.</p><h4>1. Create dataset manifest to add to catalog</h4><p>The code below creates a new dataset in the Ocean Data Platform using a manifest structure. The manifest defines:</p><ul><li>name: Dataset name in the system (no spaces, hyphens and in lowercase)</li><li>display_name: Name to be shown in the website</li><li>description: A brief description of the dataset. It&#39;s always a good idea to add a description to quickly understand what the data contains and whether it&#39;s relevant for other user&#39;s needs</li><li>Specifications for storage using a tabular storage controller and class</li><li>Maintainer contact information</li></ul><p>If the dataset already exists, it fetches the existing dataset instead of creating a duplicate. This prevents accidental duplications while ensuring the dataset is properly registered in the system.</p><pre># hub ocean client<br>client = OdpClient()<br><br>dataset_name = &quot;Atlantic and Gulf Rapid Reef Assessment - Benthic&quot;<br><br># Declare a dataset manifest to add to the catalog<br>dataset_manifest = DatasetDto(<br>    metadata = MetadataDto(<br>        name = re.sub(r&quot;\s*-\s*|\s+&quot;, &quot;-&quot;, dataset_name.lower()),<br>        display_name = dataset_name,<br>        description = &quot;Benthic cover dataset from the Atlantic and Gulf Rapid Reef Assessment (AGRRA) Program, &quot;<br>        &quot;documenting coral reef benthic community composition across sites in the wider Caribbean.&quot;,<br>        #external_url = &quot;https://obis.org/dataset/47edd06e-212a-4677-9abb-6384a0cac20a&quot;, # ------ doesn&#39;t work ------<br>        labels = {&quot;hubocean.io/test&quot;: True, &#39;tabular_v2&#39;: 1.0},<br>    ),<br>    spec = DatasetSpec(<br>        storage_controller = &quot;registry.hubocean.io/storageController/storage-tabular&quot;,<br>        storage_class = &quot;registry.hubocean.io/storageClass/tabular&quot;,<br>        maintainer = ContactInfo(<br>            contact = &quot;Brenda Varguez &lt;Brenda.Varguez@oceandata.earth&gt;&quot;,<br>            organisation = &quot;Hub Ocean&quot;,<br>        ),<br>    ),<br>)<br><br># ingest info into catalog (to know resource exist)<br>try:<br>    created_resource_manifest = client.catalog.create(dataset_manifest)<br>    print(&quot;Resource created successfully:&quot;, created_resource_manifest.qualified_name)<br><br>except OdpValidationError as e:<br>    print(&quot;Validation Error:&quot;, e)<br>    <br>except OdpResourceExistsError:<br>    created_resource_manifest = client.catalog.get(&quot;catalog.hubocean.io/dataset/&quot; + dataset_manifest.metadata.name)<br>    print(&quot;Dataset already exists. Fetched existing dataset:&quot;, created_resource_manifest.qualified_name)</pre><p>Check if the resource was correctly created: json.loads(client.catalog.get(&quot;catalog.hubocean.io/dataset/&quot; + dataset_manifest.metadata.name).json())</p><h4>2. Create schema for dataset</h4><p>Next, let&#39;s set up a schema for storing the marine species data in ODP. I defined the structure for various data fields including:</p><ul><li>Scientific names (both original and WoRMS-validated)</li><li>Taxonomic information (matching type, rank, and acceptance status)</li><li>Location data (country, latitude, longitude, depth measurements)</li><li>Temporal data (event dates)</li><li>Count data (number of individuals)</li></ul><p>Each field is carefully defined with specific data types (like strings, floats, or integers) and includes descriptive metadata to help users understand what each field represents. This schema ensures data consistency and proper organization within the platform.</p><blockquote><em>This is a sample of schema declaration, please refer to the github repository mentioned at the beginning of this post for the full code.</em></blockquote><pre># 1. Get Dto<br>table_schema = client.table_v2(<br>    client.catalog.get(&quot;catalog.hubocean.io/dataset/&quot; + dataset_manifest.metadata.name)<br>)<br><br># 2. Create new table with our desired schema<br>schema = pa.schema([<br>    #pa.field(&quot;id&quot;, pa.string(), nullable=False, metadata={&quot;index&quot;:&quot;1&quot;}),<br>    pa.field(&quot;scientific_name&quot;, pa.string(),<br>             metadata = {&quot;description&quot;: &quot;The scientific name of the species&quot;}),<br>    pa.field(&quot;valid_scientific_name&quot;, pa.string(),<br>             metadata = {&quot;description&quot;: &quot;The scientific name of the species according to WoRMS&quot;}),<br>    pa.field(&quot;latitude&quot;, pa.float64(),<br>             metadata = {&quot;description&quot;: &quot;Latitude of the observation&quot;}),<br>    pa.field(&quot;longitude&quot;, pa.float64(),<br>             metadata = {&quot;description&quot;: &quot;Longitude of the observation&quot;}),<br>    pa.field(&quot;event_date&quot;, pa.date32(),<br>             metadata = {&quot;description&quot;: &quot;Date of the observation&quot;}),<br>    pa.field(&quot;individual_count&quot;, pa.int64(),<br>             metadata = {&quot;description&quot;: &quot;Count of individuals observed&quot;})<br>])<br><br>table_schema.create(schema)<br><br># 3. Verify the new schema<br>print(table_schema.schema())</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*09EALK5TL6RiEMS9m7i_mw.png" /><figcaption>Hub Ocean dataset schema sample</figcaption></figure><h4>3. Ingest data from dataframe</h4><pre>with table_schema as tx:<br>  tx.insert(data.to_dict(orient=&#39;records&#39;))</pre><p>Print one row:</p><pre># Quick output one row to check<br>next(table_schema.select().rows())<br><br># ------ Output ------ #<br>{&#39;scientific_name&#39;: &#39;agaricia agaricites&#39;,<br> &#39;valid_scientific_name&#39;: &#39;Agaricia agaricites&#39;,<br> &#39;matching_type&#39;: &#39;accepted&#39;,<br> &#39;unaccept_reason&#39;: &#39;None&#39;,<br> &#39;rank&#39;: &#39;Species&#39;,<br> &#39;latitude&#39;: 18.4630508423,<br> &#39;longitude&#39;: -77.3581314087,<br> &#39;minimum_depth_in_meters&#39;: 9.1,<br> &#39;maximum_depth_in_meters&#39;: 9.1,<br> &#39;event_date&#39;: datetime.date(2000, 8, 4),<br> &#39;individual_count&#39;: 19,<br> &#39;country&#39;: &#39;Jamaica&#39;}</pre><p>Use .page(size = N) instead of .rows() to choose how many records you want to print.</p><h4>4. Visualize data in ODP</h4><ul><li>Log into <a href="https://app.hubocean.earth/">https://app.hubocean.earth/</a> — &gt; My Data</li><li>From the <strong>View by</strong> dropdown at the top right corner select <strong>Dataset</strong></li><li>You’ll see all your available datasets. Beware that all the datasets loaded into ODP are private by default.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nkmHEYkhPdmBqpdQXvc2iQ.png" /><figcaption>Private datasets in ODP</figcaption></figure><p>After loading a new dataset, you can edit it by updating the name, description, and labels, or by uploading new files to enrich the information. You can also preview your data and manage access permissions by adding collaborators who can either edit or view the dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VARjI3_yabHsSR_28bFa0Q.gif" /></figure><p>You can also edit your table schema by clicking on the <strong>Edit table</strong> button. See the gif below for a quick introduction, we’ll cover this part in detail in future posts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BZ1P4zJCbg8RI8RKij9yMA.gif" /></figure><h3>Conclusion</h3><p>The Ocean Data Platform by Hub Ocean represents a significant advancement in ocean data management and accessibility. By providing a standardized, user-friendly platform for storing, accessing, and analyzing marine data, it enables researchers, organizations, and other users to make more informed decisions about ocean conservation and management.</p><p>Through the step-by-step process we’ve explored, from creating dataset manifests to defining schemas and ingesting data, we can see how the platform simplifies the complex task of working with ocean data. The platform’s ability to handle various data types and its robust infrastructure, built for collaboration, makes it an invaluable tool for the marine science community.</p><p>As we continue to face growing challenges in ocean conservation and marine resource management, I believe that platforms like this will play an increasingly crucial role in understanding and protecting our oceans. The standardization and accessibility of ocean data through Hub Ocean’s platform mark an important step forward in marine science and conservation efforts.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=496736703355" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Coding Symphony: ClickHouse + dbt and table partitioning, order by and primary keys (part 3)]]></title>
            <link>https://awstip.com/coding-symphony-clickhouse-dbt-and-table-partitioning-order-by-and-primary-keys-part-3-16a25dd977ca?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/16a25dd977ca</guid>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[partitioning]]></category>
            <category><![CDATA[olap]]></category>
            <category><![CDATA[clickhousedb]]></category>
            <category><![CDATA[clickhouse]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Thu, 18 Apr 2024 18:06:23 GMT</pubDate>
            <atom:updated>2025-06-06T04:16:07.758Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*77idF5nGXXxsRmnf" /></figure><p>This tutorial assumes that you have an existing dbt project configured to work with a ClickHouse instance. If you don’t, you can refer to the <a href="https://medium.com/@brendavarguez21/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-1-edaa9ed2a457">first part of this tutorial</a> where I developed an step-by-step guide. For basic understanding of the dbt models feel free to check the <a href="https://medium.com/towardsdev/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-2-f486f9af54bd">second part</a>.</p><h3>What is table partitioning?</h3><p>Table partitioning is a database management technique where a larger table is split into smaller, more manageable pieces called partitions. This can enhance query performance by allowing the database engine to scan less data when executing a query. The partitions are typically based on certain column(s) values of the table, such as dates.</p><p>ClickHouse manages partitioning by keeping track of the minimum and maximum values for each partition. When a query is executed, ClickHouse only scans the partitions where the data might be located, based on these minimum and maximum values. This helps to reduce the amount of data that needs to be scanned, improving query performance. ClickHouse also allows for custom partitioning strategies, meaning you can partition your tables based on specific business needs or query patterns.</p><p>In ClickHouse, a “good” partition should meet several criteria:</p><ol><li>It should be based on the most frequently filtered column(s) in your queries. This allows for efficient filtering and improves query performance.</li><li>The partition key should result in a manageable number of partitions. Too many partitions can lead to excessive memory usage and reduce query performance, while too few partitions can negate the benefits of partitioning (dozens/hundreds it’s ok, not thousands).</li><li>It should distribute data evenly across partitions. Uneven distribution can lead to some partitions becoming hotspots, which can negatively impact performance.</li><li>It should align with your business needs and query patterns. For example, if your queries often filter on a specific date range, it would make sense to partition your table based on date.</li><li>A single partition should have a size range of 1–300Gb, and a simply SELECT ideally will only query a few dozens of them. Moreover, a single insert query should only insert data into one or few partitions.</li></ol><h3>Order by and primary keys</h3><p>What happens when your data doesn’t meet the required criteria but you still want to optimize the queries? You can always rely on ORDER BY keys and PRIMARY keys. In ClickHouse tables, ORDER BY keys and PRIMARY keys also play a crucial role in optimizing data retrieval and ensuring the efficiency of queries.</p><p>The ORDER BY key is used to determine the order in which data is written to a table. Data in ClickHouse is stored in the order of the ORDER BY key which makes it possible to run more efficient range queries. The PRIMARY key, on the other hand, is a subset of the ORDER BY key columns. It&#39;s used to quickly skip over data when running a SELECT query. ClickHouse uses the PRIMARY key to determine which rows match the query condition without scanning all rows in a table. This significantly reduces the amount of data to read from disk and speeds up data selection.</p><p>Good ORDER BY keys usually have from 3 to 5 columns, starting from the most important key for filtering to less important key while filtering (from lowest cardinality to highest cardinality).</p><blockquote><em>For a detailed explanation you can refer to </em><a href="https://medium.com/datadenys/how-clickhouse-primary-key-works-and-how-to-choose-it-4aaf3bf4a8b9"><em>this article</em></a><em> from Denys Golotiuk.</em></blockquote><h3>Partitioning and other keys in dbt</h3><blockquote><em>The files created in this tutorial can be found in </em><a href="https://github.com/ozeanith/medium-lab"><em>this github repository</em></a><em> under the </em><em>clickhouse_tutorials/ch_dbt folder.</em></blockquote><p>You can combine these settings in the dbt models by using the config macro. Let’s see an example.</p><p>Because my reservations table loads many megabytes of data every day, I decided to make this model incremental. For partitioning I only included one column which is the year when the reservation was made. While for primary and order by keys the quarter when the reservation was made (transaction_q), payment type, transaction type, the user_id (user that made the reservation), and the reservation_id were included. Take into account the accepted format for multiple keys is a tuple of column names. Moreover, If the primary_key is not specified, ClickHouse will use the order_by expression as the primary key.</p><p>Because this is an incremental mode, dbt will check the unique_key when new data is added to make sure that there are no duplicate entries. If a record with the same unique_key already exists, the old record will be deleted and the new record will be inserted. This is why the incremental_strategy in the config block is set to &#39;delete+insert&#39;.</p><pre>{{<br>    config(<br>        materialized = &quot;incremental&quot;,<br>        unique_key = &quot;reservation_id&quot;,<br>        primary_key = &#39;(reservation_q, transaction_payment_result, transaction_type, user_id, reservation_id)&#39;,<br>        order_by = &#39;(reservation_q,  transaction_payment_result, transaction_type, user_id, reservation_id)&#39;,<br>        settings = {&#39;allow_nullable_key&#39;: 1,<br>                    &#39;replicated_can_become_leader&#39;: True,<br>                    &#39;max_suspicious_broken_parts&#39;: 1},<br>        partition_by = &#39;reservation_year&#39;,<br>        incremental_strategy = &#39;delete+insert&#39;,<br>    )<br>}}</pre><p>Let’s analyze the rest of the code. Observe that most of my primary_keys and order_by keys were calculated and do not originally come from the source data. This is allowed because they will actually exist in the final table.</p><p>The is_incremental() macro is used to control the behavior of SQL queries in incremental models. In incremental models, dbt will only process the new or updated data since the last run, rather than rebuilding the entire table every time. This can greatly improve the efficiency of data transformation for large tables.</p><p>For the reservations model, the is_incremental() macro is used in a conditional statement ({% if is_incremental() %}) to append a WHERE clause to the query only when the model is being run in incremental mode. The WHERE clause filters out any records that were modified (last_mod) after the maximum modification time of the records currently in the table (SELECT MAX(last_mod) FROM {{ this }}). This ensures that only new or updated records are processed in each incremental run.</p><p>As mentioned in previous posts, my source data is stored in a postgres table (in my production database) and that’s why I’m using the postgresql ClickHouse table function to run SELECT statements in the remote postgres server by passing the right parameters.</p><blockquote><em>For a detailed explanation check the previous parts of this series of posts.</em></blockquote><pre>SELECT<br>    id AS reservation_id,<br>    last_mod,<br>    user_id,<br>    added AS reservation_added,<br>    date_trunc(&#39;year&#39;, added) AS reservation_year,<br>    date_trunc(&#39;quarter&#39;, added) AS reservation_q,<br>    card_id,<br>    is_cancelled,<br>    is_failed,<br>    processed_at,<br>    amount,<br>    CASE<br>        WHEN result = 0 THEN &#39;APPROVED&#39;<br>        WHEN result = 1 THEN &#39;DECLINED&#39;<br>        WHEN result = 2 THEN &#39;ERROR&#39;<br>        WHEN result = 3 THEN &#39;TIMEOUT&#39;<br>        ELSE &#39;UNKNOWN_RESULT&#39;<br>    END AS transaction_payment_result,<br>    CASE<br>        WHEN type = 0 THEN &#39;CASH&#39;<br>        WHEN type = 1 THEN &#39;CHIP&#39;<br>        WHEN type = 2 THEN &#39;SWIPE&#39;<br>        WHEN type = 6 THEN &#39;CONTACTLESS&#39;<br>        ELSE &#39;UNKNOWN_TYPE&#39;<br>    END AS transaction_type,<br>    commissions<br>FROM postgresql(&#39;db_host:5432&#39;, &#39;db_name&#39;, &#39;reservations&#39;,<br>        &quot;{{ env_var(&#39;PROD_DB_USER&#39;) }}&quot;, &quot;{{ env_var(&#39;PROD_DB_PASS&#39;) }}&quot;,<br>        &#39;public&#39;)<br>{% if is_incremental() %}<br><br>    WHERE last_mod &gt; (<br>        SELECT MAX(last_mod) FROM {{ this }}<br>        WHERE<br>            reservation_year<br>            &gt;= date_trunc(<br>                &#39;year&#39;, now(&#39;America/Mexico_City&#39;) - INTERVAL &#39;365 day&#39;<br>            )<br>            -- no record can have an smaller last_mod value than<br>            -- its reservation_added val. &#39;-1 day&#39; for update every new year<br>            -- or &#39;-365 day&#39; for all reservations added last year<br>    )<br><br>{% endif %}</pre><p>Build your model with dbt run -m {model_name}and at the very bottom of the table’s DDL you’ll see:</p><pre>PARTITION BY reservation_year<br>PRIMARY KEY (reservation_q,<br> transaction_payment_result,<br> transaction_type,<br> user_id,<br> reservation_id)<br>ORDER BY (reservation_q,<br> transaction_payment_result,<br> transaction_type,<br> user_id,<br> reservation_id)</pre><p>To check the size of each partition you can run the following query:</p><pre>SELECT<br>  partition,<br>  formatReadableSize(sum(bytes)) AS partition_size<br>FROM system.parts<br>WHERE &quot;database&quot; = &#39;your_db&#39; AND table = &#39;your_table&#39;<br>GROUP BY partition</pre><p>Note that during the first years only megabytes or kilobytes were being loaded. Starting on 2021 each partition is storing at least 5Gb.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/590/1*1hZyxMhdt781DAf3NjH62Q.png" /></figure><h3>Conclusion</h3><p>This tutorial provides a deep dive into optimizing query performance in ClickHouse with dbt through the use of table partitioning, and the strategic use of ORDER BY and PRIMARY keys. By understanding and implementing these techniques, you can significantly enhance the efficiency of your data retrieval processes. However, it&#39;s essential to choose the right keys and partitioning strategies that align with your specific data and business needs. The incremental model created in this tutorial is just a starting point for managing large and continuously growing datasets.</p><p>In conclusion, this series on “Coding Symphony” has offered a comprehensive guide to leveraging the power of ClickHouse and dbt for modern data analytics. In the first part, we laid the groundwork by setting up a dbt project with a ClickHouse instance. The second part expanded on this foundation by introducing dbt models and seeds and elaborating on their fundamental understanding. Finally, in the third part, we delved into advanced topics such as table partitioning, and the use of ORDER BY and PRIMARY keys to optimize query performance. This series not only provides a solid foundation for managing large datasets but also offers a roadmap for continuous improvements and growth in data analytics.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=16a25dd977ca" width="1" height="1" alt=""><hr><p><a href="https://awstip.com/coding-symphony-clickhouse-dbt-and-table-partitioning-order-by-and-primary-keys-part-3-16a25dd977ca">Coding Symphony: ClickHouse + dbt and table partitioning, order by and primary keys (part 3)</a> was originally published in <a href="https://awstip.com/">AWS Tip</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Coding Symphony: Unleashing the Power of ClickHouse and dbt for Modern Data Analytics (part 2)]]></title>
            <link>https://towardsdev.com/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-2-f486f9af54bd?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/f486f9af54bd</guid>
            <category><![CDATA[olap]]></category>
            <category><![CDATA[dbt-labs]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[clickhousedb]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Mon, 08 Apr 2024 13:36:06 GMT</pubDate>
            <atom:updated>2025-06-06T04:14:25.509Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fxbIUhSW_AxMYSLx.jpg" /></figure><h3>Building your firsts models and seeds</h3><blockquote><em>The files created in this tutorial can be found in </em><a href="https://github.com/ozeanith/medium-lab"><em>this github repository</em></a><em> under the </em><em>clickhouse/ch_dbtfolder.</em></blockquote><p>This tutorial assumes that you have an existing dbt project configured to work with a ClickHouse instance. If you don’t, you can refer to the <a href="https://medium.com/@brendavarguez21/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-1-edaa9ed2a457">first part of this tutorial</a> where I developed a step-by-step guide.</p><h4>dbt-seeds</h4><p>Usually seeds are used as model helpers, and they contain information required by some dbt models to continue cleaning the data.</p><p>In this case I only require a small csv with country names, country codes and a local id. This info will be used by the users model, and remember that all your csv files should be stored in your …/…/seeds path.</p><pre>country_code,country_name,local_id<br>mx,Mexico,123456789<br>co,Colombia,234567891<br>ma,Morocco,3456789101</pre><p>To build your file just run in terminal: dbt seed and you’ll see something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*THMRX-0TBS7V1ipPk3D8pA.png" /></figure><p>if you have more seeds you will see them listed in these logs. Note that dbt also shows how many records were inserted into the country_codes table. In addition, observe that the table was build in the warehouse_dev database by default because the dev profile (within profiles.yml) was declared as the default target, and remember that this profile points to warehouse_dev.</p><h4>dbt-models</h4><p>The information retrieved by my models comes from two different production PostgreSQL databases hosted in two different IP addresses. Before using ClickHouse I was also using PostgreSQL as my analytics database. This means that in order for dbt to have access to this data first I had to copy the information from the production databases to the analytics database with pg_dump (postgres prd → postgres analytics), so then I could have all this information living in the same host for dbt be able:</p><ul><li>to query the prod info</li><li>transform and load the pre-processed data into the analytics warehouse</li></ul><p>Some of the drawbacks of this solution is that I had the feeling that I was “duplicating” the information, and therefore, a lot of disk space was being wasted. Nevertheless, the main issue was that just the copying process for postgres prd → postgres analytics used to last around 2hrs/day. Furthermore, the duration of the data cleaning process with dbt used to be of 3hrs/day which made the whole process to be running for a minimum of 5hrs/day (copying + cleaning).</p><p>Unlike PostgreSQL, ClickHouse offers a function that connects to a remote postgres server and executes SELECT and INSERT queries without the requirement of storing the raw data in the same host. The <a href="https://clickhouse.com/docs/en/sql-reference/table-functions/postgresql">postgresql</a> function receives as inputs:</p><ul><li>the postgres server address (host:port)</li><li>name of the database and table where your data is living (database &amp; table)</li><li>your credentials to connect to the remote server (user and password)</li><li>the schema as optional parameter</li></ul><p>In other words, this function allowed me to run some queries in dbt with clickhouse as my analytics warehouse, and without having to copy the data from postgres prd → clickhouse analytics. Moreover, the duration of the data cleaning process with dbt now is only 41 minutes and I basically got rid of the copying step.</p><h4>Users</h4><p>Note that for this model I’m joining with the country_codes table, that is basically a seed, which allows me to extract the name of the country where each user comes from. Additionally, see how I’m implementing the postgresql function to query my data from my production database, at the same time that I’m using once again the env_var macro to extract the user and passwords variables instead of hard coding them in the code.</p><pre>{{<br>    config(<br>        unique_key = &#39;user_id&#39;,<br>        pre_hook = &#39;TRUNCATE TABLE IF EXISTS {{ this }} ON CLUSTER default&#39;<br>    )<br>}}<br><br>SELECT<br>    u.id AS user_id,<br>    u.added AS date_created,<br>    cc.country_name AS country_name<br>FROM postgresql(&#39;host:5432&#39;, &#39;database_name&#39;, &#39;users&#39;,<br>        &quot;{{ env_var(&#39;PROD_DB_USER&#39;) }}&quot;, &quot;{{ env_var(&#39;PROD_DB_PASS&#39;) }}&quot;,<br>        &#39;public&#39;) AS u<br>LEFT JOIN {{ ref(&#39;country_codes&#39;) }} AS cc ON u.market_id = cc.country_code</pre><h4>Promotions</h4><p>In this model, even though I’m specifying that it should be materialized as a table, we can consider the materialized parameter as redundant because I previously specified that all the models in the models/prd/ folder should be materialized as tables.</p><pre>{{<br>    config(<br>      materialized = &quot;table&quot;,<br>        pre_hook = &quot;TRUNCATE TABLE IF EXISTS {{ this }} ON CLUSTER default&quot;,<br>    )<br>}}<br><br>SELECT<br>    id AS promotion_id,<br>    uuid,<br>    shipping_delay_days AS shipment_days,<br>    amount_shipping / 100 AS shipping_amount,<br>    amount_net / 100 AS item_price,<br>    description_short AS promo_name,<br>    round(discount_percentage_display * 100 / (amount_net / 100), 2)<br>        AS discount_percentage,<br>    discount_percentage_display AS discount_amount<br>FROM postgresql(&#39;host.db:5432&#39;, &#39;database_name&#39;, &#39;promotions&#39;,<br>        &quot;{{ env_var(&#39;PROD_DB_USER&#39;) }}&quot;, &quot;{{ env_var(&#39;PROD_DB_PASS&#39;) }}&quot;,<br>        &#39;public&#39;)</pre><h4>Promotions orders</h4><p>For the promotions_orders table see that I’m using the postgresql function twice because I want to join two tables from the same postgres server into one table in ClickHouse.</p><pre>{{<br>    config(<br>        materialized = &quot;table&quot;,<br>        engine = &quot;ReplicatedMergeTree&quot;,<br>        pre_hook = &quot;TRUNCATE TABLE IF EXISTS {{ this }} ON CLUSTER default&quot;,<br>    )<br>}}<br><br>SELECT<br>    po.id AS order_id,<br>    po.added AS order_added,<br>    po.shipped_at AS shipment_date,<br>    po.status AS order_status,<br>    po.amount / 100 AS total_price,<br>    po.payment_type AS payment_type,<br>    po.loan_id AS loan_id,<br>    po.shop_id AS shop_id,<br>    pmi.quantity AS items_quantity,<br>    pmi.promotion_id AS promotion_id<br>FROM postgresql(&#39;host:5432&#39;, &#39;database_name&#39;, &#39;promotions_productorder&#39;,<br>        &quot;{{ env_var(&#39;PROD_DB_USER&#39;) }}&quot;, &quot;{{ env_var(&#39;PROD_DB_PASS&#39;) }}&quot;,<br>        &#39;public&#39;) AS po<br>LEFT JOIN postgresql(&#39;host:5432&#39;, &#39;database_name&#39;, &#39;promotions_orderitem&#39;,<br>        &quot;{{ env_var(&#39;PROD_DB_USER&#39;) }}&quot;, &quot;{{ env_var(&#39;PROD_DB_PASS&#39;) }}&quot;,<br>        &#39;public&#39;) AS pmi ON po.id = pmi.order_id</pre><h4>Ratings</h4><p>In the ratings model I’m querying data from a different database that is also living in another host and therefore requires another set of credentials. See that the user and password env variables are named differently because they have other values.</p><p>Also, note that I’m also using materialized = &quot;table&quot; and this is because in dbt_project.yml I specified that views are the default materialization for the models in the models/google_analytics/ folder.</p><pre>{{<br>    config(<br>        materialized = &quot;table&quot;,<br>        pre_hook = &quot;TRUNCATE TABLE IF EXISTS {{ this }} ON CLUSTER default&quot;,<br>    )<br>}}<br><br>SELECT<br>    id AS score_uuid,<br>    &#39;app&#39; AS product,<br>    score,<br>    shop_id AS user_id,<br>    comment,<br>    parseDateTime64BestEffortOrNull(createdAt, 4, &#39;America/Mexico_City&#39;)<br>        AS score_added<br>FROM postgresql(&#39;host2:5432&#39;, &#39;database_name2&#39;, &#39;rating&#39;,<br>        &quot;{{ env_var(&#39;PROD_DB2_USER&#39;) }}&quot;, &quot;{{ env_var(&#39;PROD_DB2_PASS&#39;) }}&quot;,<br>        &#39;public&#39;)</pre><p>Once you have all your models ready, you can run dbt run -m {model_name} to build just one model. You can use dbt run -m {folder_name} to build all the models inside a folder, or simply use dbt run to build all your models. Example output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3PrRMiMpnf-PpEmL9qBpgQ.png" /></figure><p>As you can see, all the tables were also built in the warehouse_dev database. To specify the target to be used, either by dbt seed or dbt run, use the -t flag with the target name: Example: dbt run -m ratings -t prd</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5oyRbLA77S6RhTgyd0g_Rw.png" /></figure><p>dbt seed -t prd</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wpkXKwQoS6q9CpnhnuP4Iw.png" /></figure><p>dbt run -m prd -t prd</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L3t_dMWwGow0PFgUAaDbmw.png" /></figure><p>And you’re done! You builded your first models and seeds in CH using dbt. In conclusion, by integrating dbt with ClickHouse, users can leverage its capabilities to enhance their workflows and achieve more efficient and powerful data analytics.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f486f9af54bd" width="1" height="1" alt=""><hr><p><a href="https://towardsdev.com/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-2-f486f9af54bd">Coding Symphony: Unleashing the Power of ClickHouse and dbt for Modern Data Analytics (part 2)</a> was originally published in <a href="https://towardsdev.com/">Towards Dev</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Coding Symphony: Unleashing the Power of ClickHouse and dbt for Modern Data Analytics (part 1)]]></title>
            <link>https://medium.com/@adrevant/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-1-edaa9ed2a457?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/edaa9ed2a457</guid>
            <category><![CDATA[olap]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[dbt-labs]]></category>
            <category><![CDATA[clickhousedb]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Mon, 08 Apr 2024 13:35:53 GMT</pubDate>
            <atom:updated>2025-06-06T04:11:21.389Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fxbIUhSW_AxMYSLx.jpg" /></figure><p>dbt (data build tool) is a popular data transformation and modeling tool. It allows data engineers to define and execute transformations on their data, enabling them to create reliable and reusable data pipelines. With dbt, users can build and maintain data models, apply business logic to their data, and transform raw data into a structured and analysis-ready format. dbt also provides features for testing and documenting data models, ensuring data quality and facilitating collaboration among team members.</p><h3>Prerequisites</h3><p>1. This tutorial assumes that you already have a ClickHouse instance up and running, and stable enough to connect to dbt. If you don’t have one, feel free to review my previous post <a href="https://medium.com/@brendavarguez21/deploying-clickhouse-on-a-kubernetes-cluster-using-terraform-d4e2234c27af">Deploying ClickHouse on a Kubernetes cluster using Terraform</a>, where I show the first steps to deploy a CH instance.</p><p>2. Create a python virtual environment and install both dbt-coreand dbt-clickhouse. As of today, these are the latest versions.</p><pre>python3 -m venv my_env<br>pip install dbt-core==1.7.7<br>pip install dbt-clickhouse==1.7.2</pre><p>3. Create a new dbt project with dbt initand give it a name. This will generate a bunch of folders with some example scripts that can be used as templates.</p><h3>Configuring dbt</h3><blockquote><em>The files created in this tutorial can be found in </em><a href="https://github.com/ozeanith/medium-lab"><em>this github repository</em></a><em> under the </em><em>clickhouse_tutorials/ch_dbt folder.</em></blockquote><h4>dbt profile</h4><p>Let’s begin by creating a profiles.ymlfile where dbt will find the connection details to connect to ClickHouse (or any other database or data platform). For further documentation about profiles <a href="https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml">see</a>. For CH specifics <a href="https://docs.getdbt.com/docs/core/connect-data-platform/clickhouse-setup#connecting-to-clickhouse-with-dbt-clickhouse">see</a>.</p><p>I’ll create two configs, one called devfor testing locally, and another one called prdto read and write to the production CH databases. As you can see, both outputs are pretty similar, the only configs that should change are:</p><ul><li>host: host of your CH instance</li><li>schema: ClickHouse database where your models will be built. warehouse_dev for local tests, and prod_db for production. These databases are the default ones, however, if you want to create a model in a different database you can specify it in the model macro. We’ll see this later.</li><li>user &amp; password: You should have at least two sets of credentials (users and passwords) to provide different access levels. For example, the user you use for testing shouldn’t have access to your production databases. For more info, review my last post <a href="https://medium.com/aws-tip/creating-multiple-users-in-clickhouse-from-terraform-998ccfa6c44a">Creating multiple users in ClickHouse from Terraform</a>.</li><li>In addition, credentials are not hard coded, they are passed as environment variables, i.e., I export them by running export CLICKHOUSE_USER=username and export CLICKHOUSE_PASS=string in the terminal. Then, dbt is able to read them with the env_var function, which is used to incorporate environment variables from the system. This function can be used almost anywhere.</li></ul><p>My ClickHouse instance consists of 1 shard with 3 replicas. For that reason, when creating a new table I need to make sure that it’s created in all replicas. To do that I’m creating them with the ReplicatedMergeTree engine, but additionally it is required to add the ON CLUSTER {cluster_name} clause in each query. So, instead of individually adding that setting into every model, it can be easily specified in this file with the cluster option. I will further explain why this engine was chosen in the #seeds section.</p><pre>analytics:<br>  target: dev # default schema where data will be sent<br><br>  outputs:<br>    dev: # this one will be used for testing locally<br>      type: clickhouse<br>      schema: warehouse_dev # Default ClickHouse database where your models will be built<br>      user: &quot;{{ env_var(&#39;CLICKHOUSE_DEV_USER&#39;) }}&quot;<br>      password: &quot;{{ env_var(&#39;CLICKHOUSE_DEV_PASS&#39;) }}&quot;<br>      port: 9000<br>      host: localhost<br>      cluster: default<br>      retries: 1<br>      threads: 4 # max number of models to run at the same time<br>      connect_timeout: 10<br>      send_receive_timeout: 3600 # 1hr<br>      use_lw_deletes: True # `delete+insert` as the default incremental strategy<br>    <br>    prd:<br>      type: clickhouse<br>      schema: prod_db<br>      user: &quot;{{ env_var(&#39;CLICKHOUSE_USER&#39;) }}&quot;<br>      password: &quot;{{ env_var(&#39;CLICKHOUSE_PASS&#39;) }}&quot;<br>      port: 9000<br>      host: clickhouse.data.svc # service-name.namespace.svc (K8s)<br>      cluster: default<br>      retries: 1<br>      threads: 4<br>      connect_timeout: 10<br>      send_receive_timeout: 3600 # 1hr<br>      use_lw_deletes: True # `delete+insert` as the default incremental strategy<br><br>config:<br>  send_anonymous_usage_stats: false</pre><h4>dbt project</h4><p><strong>General configs</strong></p><p>We will also need a dbt_project.yml file. It tells dbt how to operate your project, the path to your models, seeds, macros, etc. Unlike the profiles files, this one should have been previously created when you ran dbt init. In my case, I’ll add some extra configs that will be useful according to my ClickHouse set up.</p><p>According to dbt itself, a project name should be your organization’s name or the purpose of the models. Also, make sure to pass the target profile declared in profiles.yml. You will also need to specify where dbt should look for different types of files. E.g., the model-paths config states that models in this project can be found in the models/ directory. However, it’s highly unlikely that you need to change any of those configs.</p><p><strong>Models</strong></p><p>There are two ways of adding up extra settings to your models:</p><ul><li>By individually declaring them within specific models using the &#39;{{ config(...) }}&#39; macro.</li><li>By including them in this file.</li></ul><p>I strongly advise you to add in here the global configs, meaning, the configs that you want to use to affect all models. On the other hand, if there is a setting you need to specifically affect one or a few models, then you’d want to use &#39;{{ config(...) }}&#39; in that model. We’ll review an example of this later.</p><p>As previously mentioned, I’m using the ReplicatedMergeTree in the query used to create my dbt models. I’m making that specification here and adding + as a prefix to indicate that this config should be applied to all my models. Moreover, I need some models to be built as tables, and others as views. So, as you can see, I’m also declaring some extra configs for my two sub-folders inside models\\ . Everything inside the prd\\ folder will be built as a table, while everything inside google_analytics\\ will be built as a view.</p><p>Just take into account that whatever you’re defining here can be overridden by the &#39;{{ config(...) }}&#39; macro.</p><p><strong>Seeds</strong></p><p>Seeds are CSV files that dbt can load into your warehouse. Using seeds is recommended when the information within those files is not sensitive, and when the data is static or when its frequency change is low.</p><p>Within the seeds/CSV files you cannot add extra settings like in the models. So you will need to declare them in this file. By default, dbt will build your seed files as tables, and will use the MergeTree engine to create them. However, with that engine the tables will not be present in all nodes within my cluster, so I also need to specify that my desired engine is ReplicatedMergeTree , by using the option +engine as global setting within seeds configs.</p><p>You can continue individually customizing both models and seeds in a properties file. I’ll show you how in other posts.</p><pre># project name<br>name: &#39;ch_dbt&#39;<br>version: &#39;1.0.0&#39;<br>config-version: 2<br><br># This setting configures which &quot;profile&quot; dbt uses for this project (profiles.yml).<br>profile: &#39;analytics&#39;<br><br># These configurations specify where dbt should look for different types of files.<br>model-paths: [&quot;models&quot;]<br>analysis-paths: [&quot;analyses&quot;]<br>test-paths: [&quot;tests&quot;]<br>seed-paths: [&quot;seeds&quot;]<br>macro-paths: [&quot;macros&quot;]<br>snapshot-paths: [&quot;snapshots&quot;]<br><br>clean-targets:         # directories to be removed by `dbt clean`<br>  - &quot;target&quot;<br>  - &quot;dbt_packages&quot;<br><br><br># Configuring models<br># Full documentation: https://docs.getdbt.com/docs/configuring-models<br><br># dbt will build all models in the prd/ directory as tables.<br># These settings can be overridden in the individual model<br># files using the `{{ config(...) }}` macro.<br>models:<br>  +engine: ReplicatedMergeTree<br>  ch_dbt: # name of your project<br>    # Config indicated by + and applies to all files under<br>    # models/prd/ folder<br>    prd:<br>      +materialized: table<br>      <br>    # Config indicated by + and applies to all files under<br>    # models/google_analytics/ folder<br>    google_analytics:<br>      +materialized: view<br><br>seeds:<br>  +engine: ReplicatedMergeTree</pre><p>Congrats! You’ve just successfully created your first dbt project and connected dbt with ClickHouse. Check the <a href="https://medium.com/@brendavarguez21/coding-symphony-unleashing-the-power-of-clickhouse-and-dbt-for-modern-data-analytics-part-2-f486f9af54bd">second part of this post</a> to learn how to build your first dbt models and seeds, and continue adding a bit more of configs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=edaa9ed2a457" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating multiple users in ClickHouse from Terraform]]></title>
            <link>https://awstip.com/creating-multiple-users-in-clickhouse-from-terraform-998ccfa6c44a?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/998ccfa6c44a</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[clickhousedb]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[terraform]]></category>
            <category><![CDATA[olap]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Thu, 08 Feb 2024 01:16:55 GMT</pubDate>
            <atom:updated>2025-06-06T04:12:21.080Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*ZlmSuKYghLGMxQvdvs23GA.png" /></figure><p>Giving different accesses to different users in a database allows for better security and control over the data. It ensures that each user has only the necessary permissions to perform their specific tasks, reducing the risk of unauthorized access or accidental data modification. Additionally, it helps maintain data integrity and supports the principle of least privilege, where users are granted only the minimum level of access required to perform their duties.</p><p>This tutorial will show you how to create different users depending on the data they need access to. Furthermore, it is assumed that you already have a terraform script that manages your ClickHouse server deployment. In this case I will update the files created in my previous post <a href="https://medium.com/@brendavarguez21/deploying-clickhouse-on-a-kubernetes-cluster-using-terraform-d4e2234c27af"><em>Deploying ClickHouse on a Kubernetes cluster using Terraform</em></a>.</p><h3>Users descriptions</h3><h4>CI/CD user</h4><p>I use <a href="https://www.getdbt.com/product/what-is-dbt">dbt (data build tool)</a> as a kind of orchestrator to automate and improve my data transformation and integration processes in my data warehouse. In simple words, a set of sql scripts are run using dbt to extract and transform raw data. In addition, dbt may (or may not) be in charge of creating the tables and inserting the data into these tables.</p><p>In my CI/CD pipeline, among other jobs, dbt needs to perform some steps to check (as much as possible) if these scripts will run with no errors in production. Therefore, this user requires a connection to my dev database to run queries such as SELECT, CREATE and INSERT.</p><h4>Metabase user</h4><p><a href="https://www.metabase.com/">Metabase</a> is a business intelligence tool that allows users to create charts and dashboards from different data sources including ClickHouse. Then, the metabase user only needs to visualize and manipulate the data from my different databases.</p><h4>Production user</h4><p>Unlike the CI/CD user, my production user is required to access the other databases but the queries to be run should be the same and therefore the permissions.</p><h3>Creating my ClickHouse users</h3><blockquote><em>The files created or updated in this tutorial can be found in </em><a href="https://github.com/ozeanith/medium-lab"><em>this github repository</em></a><em> under the </em><em>clickhouse_tutorials/</em><em>clickhouse_users folder.</em></blockquote><p>To create these users you will need to add the param extraOverrides where we will add the necessary grants according to the different needs per user.</p><p>1. Create a password per user.</p><pre># for gitlab ci/cd pipeline<br>resource &quot;random_password&quot; &quot;ci_password&quot; {<br>  length  = 40<br>  special = false<br>}<br><br># for metabase tool<br>resource &quot;random_password&quot; &quot;metabase_pwd&quot; {<br>  length  = 40<br>  special = false<br>}<br><br>resource &quot;random_password&quot; &quot;prd_password&quot; {<br>  length  = 40<br>  special = false<br>}</pre><p>2. Create the users using the extraOverrides param. For more info <a href="https://clickhouse.com/docs/en/operations/settings/settings-users#grants-user-setting">see</a>.</p><blockquote><em>Note that I’m already defining the name of the role that will be granted to the users but the privileges assigned to them will be defined in the next step.</em></blockquote><p>Observe that first you need to declare the label tag &lt;users&gt;&lt;/users&gt; and then declare other labels for each user, for example, &lt;ci_user&gt;&lt;/ci_user&gt;. After adding the corresponding label tag for your new user(s) don’t forget to add both password and grants labels.</p><p>See how I’m referencing the passwords created in the previous step within &lt;password&gt;&lt;/password&gt;. In addition, the only query inside &lt;grants&gt; is the one used to assign the corresponding role per user.</p><pre>&lt;users&gt;<br> &lt;ci_user&gt;<br>  &lt;password&gt;${random_password.ci_password.result}&lt;/password&gt;<br>    &lt;grants&gt;<br>     &lt;query&gt;GRANT dev_role&lt;/query&gt;<br>    &lt;/grants&gt;<br>   &lt;/ci_user&gt;<br>   &lt;metabase&gt;<br>    &lt;password&gt;${random_password.metabase_pwd.result}&lt;/password&gt;<br>     &lt;grants&gt;<br>      &lt;query&gt;GRANT metabase_role&lt;/query&gt;<br>     &lt;/grants&gt;<br>   &lt;/metabase&gt;<br>    &lt;dbt_prd&gt;<br>     &lt;password&gt;${random_password.prd_password.result}&lt;/password&gt;<br>      &lt;grants&gt;<br>       &lt;query&gt;GRANT prd_role&lt;/query&gt;<br>      &lt;/grants&gt;<br>    &lt;/dbt_prd&gt;<br>&lt;/users&gt;</pre><p>3. Defining the permission per role. Note that the dev_role and the prd_role have the same privileges but on different databases. You can consult the full list of privileges <a href="https://clickhouse.com/docs/en/sql-reference/statements/grant#privileges">here</a>.</p><p>Just like above, you need to include the label tag &lt;roles&gt;&lt;/roles&gt; so then you can add the other labels for each role. In this case you will only need the label &lt;grants&gt; so you can declare the necessesary grants with &lt;query&gt;.</p><pre>&lt;roles&gt;<br> &lt;dev_role&gt;<br>  &lt;grants&gt;<br>    &lt;query&gt;GRANT SHOW DATABASES ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT SELECT ON *.*&lt;/query&gt;<br>    &lt;query&gt;GRANT CREATE ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT TRUNCATE ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT INSERT ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT DROP TABLE, DROP VIEW ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT SYSTEM SYNC REPLICA ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT ALTER DELETE ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT ALTER UPDATE ON warehouse_dev.*&lt;/query&gt;<br>    &lt;query&gt;GRANT CREATE TEMPORARY TABLE, POSTGRES ON *.*&lt;/query&gt;<br>    &lt;query&gt;GRANT REMOTE ON *.*&lt;/query&gt;<br>  &lt;/grants&gt;<br> &lt;/dev_role&gt;<br> &lt;metabase_role&gt;<br>   &lt;grants&gt;<br>     &lt;query&gt;GRANT SHOW DATABASES ON *.*&lt;/query&gt;<br>     &lt;query&gt;GRANT SELECT ON *.*&lt;/query&gt;<br>   &lt;/grants&gt;<br> &lt;/metabase_role&gt;<br> &lt;prd_role&gt;<br>  &lt;grants&gt;<br>    &lt;query&gt;GRANT SELECT ON *.*&lt;/query&gt;<br>    &lt;query&gt;GRANT REMOTE ON *.*&lt;/query&gt;<br>    &lt;!-- prod_db --&gt;<br>    &lt;query&gt;GRANT SHOW DATABASES ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT CREATE ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT CREATE TABLE ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT TRUNCATE ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT INSERT ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT DROP TABLE, DROP VIEW ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT SYSTEM SYNC REPLICA ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT ALTER DELETE ON prod_db.*&lt;/query&gt;<br>    &lt;query&gt;GRANT ALTER UPDATE ON prod_db.*&lt;/query&gt;<br>    &lt;!-- g_analytics db --&gt;<br>    &lt;query&gt;GRANT CREATE ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT CREATE TABLE ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT TRUNCATE ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT INSERT ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT DROP TABLE, DROP VIEW ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT SYSTEM SYNC REPLICA ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT ALTER DELETE ON g_analytics.*&lt;/query&gt;<br>    &lt;query&gt;GRANT ALTER UPDATE ON g_analytics.*&lt;/query&gt;<br>    &lt;!-- global --&gt;<br>    &lt;query&gt;GRANT CREATE TEMPORARY TABLE, POSTGRES ON *.*&lt;/query&gt;<br>  &lt;/grants&gt;<br> &lt;/prd_role&gt;<br>&lt;/roles&gt;</pre><p>4. Now that we have updated our terraform files we need to apply these changes by running terraform apply. I strongly advise you to review the changes before confirming. Once you have successfully applied these changes you can check your pod logs to make sure everything is working well, and then you can proceed to make some tests with your new users.</p><blockquote><em>To know the password of any of the users you can go back to the </em><em>main.tf file and add the following piece of code:</em></blockquote><pre>output &quot;pass&quot; {<br>  value     = random_password.metabase_pwd.result<br>  sensitive = true<br>}</pre><blockquote><em>And then run </em><em>terraform output -raw pass to get the value of your password.</em></blockquote><p>And that’s it! This is how easy you can customize your ClickHouse deployment to update your original configuration, including the creation of new users. Now you should run some tests with your new users and continue customizing them if necessary.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=998ccfa6c44a" width="1" height="1" alt=""><hr><p><a href="https://awstip.com/creating-multiple-users-in-clickhouse-from-terraform-998ccfa6c44a">Creating multiple users in ClickHouse from Terraform</a> was originally published in <a href="https://awstip.com/">AWS Tip</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deploying ClickHouse on a Kubernetes cluster using Terraform]]></title>
            <link>https://towardsdev.com/deploying-clickhouse-on-a-kubernetes-cluster-using-terraform-d4e2234c27af?source=rss-c824aca476f8------2</link>
            <guid isPermaLink="false">https://medium.com/p/d4e2234c27af</guid>
            <category><![CDATA[terraform]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[clickhousedb]]></category>
            <category><![CDATA[bitnami]]></category>
            <dc:creator><![CDATA[Brenda Varguez]]></dc:creator>
            <pubDate>Sat, 27 Jan 2024 05:15:42 GMT</pubDate>
            <atom:updated>2025-06-06T04:12:53.171Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*km3mDjON9z3QK7-olxJxiA.jpeg" /></figure><p>ClickHouse is a popular open-source analytical database management system. Deploying ClickHouse on a Kubernetes cluster can provide scalability and flexibility for managing large amounts of data. In this guide, we will walk through the process of deploying a ClickHouse server on a Kubernetes cluster using Terraform.</p><h3>Why integrate ClickHouse with Kubernetes and Terraform?</h3><p>Integrating ClickHouse (CH) with Kubernetes and Terraform offers several benefits:</p><ol><li><strong>Scalability</strong>: Kubernetes allows users to easily scale ClickHouse resources up or down based on demand. You can add or remove replicas as needed to handle increasing or decreasing data volumes.</li><li><strong>Improved resource utilization:</strong> Kubernetes dynamically allocates and manages compute resources based on their availability. This ensures that resources are efficiently utilized, reducing costs and improving overall system performance.</li><li><strong>Flexibility</strong>: Kubernetes provides a flexible and modular environment for deploying and managing ClickHouse. You can take advantage of Kubernetes features such as auto-scaling, load balancing, and rolling updates to ensure optimal performance and availability.</li><li><strong>Infrastructure-as-Code</strong>: Terraform enables you to define and manage your ClickHouse infrastructure using code (in a single or multiple scripts). This allows for version control, reproducibility, and easy collaboration with other team members. With Terraform, you can provision and configure ClickHouse resources consistently across multiple environments.</li></ol><p>By integrating ClickHouse with Kubernetes and Terraform, you can leverage the scalability and flexibility of Kubernetes while following infrastructure-as-code practices with Terraform. This combination simplifies the deployment and management of ClickHouse, making it an ideal choice for handling large amounts of data in an analytical database.</p><h3>What is a helm chart?</h3><p>Before starting coding, let’s define important concepts that will be implemented later. <a href="https://helm.sh/">Helm</a> is a package manager for Kubernetes. For example, you usually use Homebrew or apt to install applications on a pc, however, helm is used to easily install and update applications running on a Kubernetes cluster.</p><p>Usually, applications consist of different components that need to be running to work correctly. If you decide to deploy each one of them on your own, it will require for you to create and manage many manifest files (usually YAML files) to define all the different components (such as pod deployments, config maps, secrets) in order to host your app on Kubernetes.</p><p>Therefore, doing all this from scratch would require an enormous effort. Then, instead of building these configurations yourself, you can simply use a helm chart. A helm chart is just a collection of YAML files (written by someone else) that have been bundled together. Helm charts make use of templates, which allow users customize their applications according to their needs by providing their own values to override the default ones. These charts are usually available in public repositories.</p><h3>Bitnami charts</h3><p>Bitnami charts are pre-configured templates and configurations that simplify the deployment process of applications on a Kubernetes cluster. <a href="https://bitnami.com/stacks/helm">Bitnami</a> has a wide variety of applications in its catalog including <a href="https://artifacthub.io/packages/helm/bitnami/clickhouse">ClickHouse</a> and <a href="https://artifacthub.io/packages/helm/bitnami/zookeeper">Apache Zookeeper</a>, and are commonly used to simplify the deployment process.</p><h3>Prerequisites</h3><ol><li>This tutorial assumes that you have a Kubernetes cluster set up and you are already connected to it.</li><li>Make sure you have <a href="https://developer.hashicorp.com/terraform/tutorials/aws-get-started/install-cli#install-terraform">Terraform installed</a> on your system as we will use Terraform to provision the necessary resources for deploying ClickHouse, such as the Kubernetes namespaces, pods, and services. To check your current version run on terminal: terraform version</li></ol><pre>Terraform v1.4.1<br>on darwin_arm64<br><br>Your version of Terraform is out of date! The latest version<br>is 1.6.6. You can update by downloading from https://www.terraform.io/downloads.html</pre><h3>Terraform files</h3><p>Next, we will create a Terraform configuration file to define the ClickHouse deployment. This file will specify the desired state of the ClickHouse resources, including the number of replicas, resource limits, and any additional configurations.</p><blockquote><em>The files created in this tutorial can be found in </em><a href="https://github.com/ozeanith/medium-lab"><em>this github repository</em></a><em> under the </em><em>clickhouse_tutorials/clickhouse_terraform folder.</em></blockquote><p>1. First, we will create the main.tf file. This file will contain the main set of configurations for your module. You can also create other configuration files and organize them however makes sense for your project.</p><pre>locals {<br>  # your cluster&#39;s namespace where all CH<br>  # services will be running<br>  namespace = &quot;stg&quot;<br>}</pre><p>Enjoying so far? give a tip</p><p><a href="https://paypal.me/brendavarguez?source=about_page-------------------------------------">Pay Brenda Martin using PayPal.Me</a></p><p>2. Next, we have to create another .tf file describing all the services required by CH to be up and running. Let’s break down this file to have a little more understanding.</p><ul><li>Creating CH super user</li></ul><pre># create CH super user<br>resource &quot;random_password&quot; &quot;clickhouse_password_test&quot; {<br>  length  = 40<br>  special = false<br>}</pre><ul><li>Declaring configurations of our ClickHouse server</li></ul><p>In the code below, see that the k8s namespace where ClickHouse will be running is the one previously declared in the main.tf file. In addition, note that the password created above is being passed as a variable for the brenda user. This user will be ClickHouse’s super user, so be careful with not sharing it with others or store the credentials where more people have access.</p><p>In this case, one server with three replicas are being created (replicaCount). Avoid creating an even number of replicas as it increases the likelihood of inconsistent decision-making. It should always be an odd number to still function after the loss of one instance:</p><ul><li>3 -&gt; tolerate 1 failure</li><li>5 -&gt; tolerate 2 failures</li></ul><p>Regarding resource’s limits, perhaps you’d want a limited amount while testing. You can always come back to increase these limits. Have in mind that the recommended amount of RAM is 32 GB or more, however, it can still run in systems with limited RAM but with low ingestion rate.</p><p>ClickHouse uses a zookeeper to coordinate data replication and distributed DDL queries execution. ClickHouse has its own zookeeper called <em>ClickHouse Keeper</em>, but as of today, there is no existing chart for this keeper. For that reason, it was decided to disable the CH keeper and use Apache’s zookeeper as an external zookeeper. Make sure you declare this dependency so CH “knows” about it.</p><p>Finally, the metric’s monitoring was enabled so we could keep track of the use of CH resources.</p><pre>resource &quot;helm_release&quot; &quot;clickhouse-test&quot; {<br>  namespace = local.namespace<br>  name      = &quot;clickhouse-test&quot;<br><br>  repository = &quot;https://charts.bitnami.com/bitnami&quot;<br>  chart      = &quot;clickhouse&quot;<br>  version    = &quot;4.1.13&quot; # bitnami clickhouse helm chart version<br><br>  timeout = 90<br>  wait    = false<br><br>  values = [<br>    &lt;&lt;EOF<br>global:<br>  storageClass: &quot;gp3&quot; # for persistence volumes<br>image:<br>  registry: docker.io<br>  repository: bitnami/clickhouse<br>  tag: 23.11.2-debian-11-r1 # clickhouse version<br>  pullPolicy: IfNotPresent<br>shards: 1<br>replicaCount: 3<br>resources:<br>  limits:<br>    memory: 6Gi<br>  requests:<br>    cpu: 500m<br>    memory: 4Gi<br>auth:<br>  username: brenda<br>  password: ${random_password.clickhouse_password_test.result}<br><br>keeper:<br>  enabled: false<br><br>persistence:<br>  enabled: true<br>  accessModes:<br>    - ReadWriteOnce<br>  size: 100Gi<br>  <br>metrics:<br>  enabled: true<br>  serviceMonitor:<br>    enabled: true<br><br>zookeeper:<br>  enabled: false<br><br>externalZookeeper:<br>  servers:<br>    - zookeeper-test.stg # name (provider&#39;s context)<br>  port: 2181<br>    EOF<br>  ]<br>  depends_on = [helm_release.zookeeper-test] # resource name (terrafomr context)<br>}</pre><ul><li>Deploying a zookeeper</li></ul><p>Remember to deploy the zookeeper service in the same namespace where your CH server was deployed.</p><p>When enabled, ZooKeeper auto purge feature retains the number of most recent snapshots and the corresponding transaction logs specified by the snapRetainCount in the interval period declared in the purgeInterval.</p><pre>resource &quot;helm_release&quot; &quot;zookeeper-test&quot; {<br>  namespace = local.namespace<br>  name      = &quot;zookeeper&quot;<br><br>  repository = &quot;https://charts.bitnami.com/bitnami&quot;<br>  chart      = &quot;zookeeper&quot;<br>  version    = &quot;12.3.4&quot; # bitnami zookeeper helm chart version<br><br>  timeout = 90<br>  wait    = false<br><br>  values = [<br>    &lt;&lt;EOF<br>global:<br>  storageClass: &quot;gp3&quot; # for persistance volumes<br><br>replicaCount: 1<br>image:<br>  registry: docker.io<br>  repository: bitnami/zookeeper<br>  tag: 3.8.3-debian-11-r3 # zookeeper version<br><br>autopurge:<br>  snapRetainCount: 3<br>  purgeInterval: 72<br><br>resources:<br>  limits:<br>    memory: 512Mi<br>  requests:<br>    memory: 512Mi<br>    cpu: 50m<br><br>persistence:<br>  enabled: true<br>  storageClass: gp3<br>  accessModes:<br>    - ReadWriteOnce<br>  size: 1Gi<br>    EOF<br>  ]</pre><ul><li>EKS cluster</li></ul><p>Finally, you have to create another .tf file with the terraform requirements such as providers and other configurations to connect with your own cluster. Mine is called verstion.tf and you can find it in the repo above.</p><blockquote><em>To know the password of your main user you can go back to the </em><em>main.tf file and add the following piece of code:</em></blockquote><pre>output &quot;pass&quot; {<br>	value     = random_password.clickhouse_password_test.result<br>	sensitive = true<br>}</pre><blockquote><em>And then run </em><em>terraform output -raw pass to get the value of your password.</em></blockquote><p>3. Now that we have our files ready, run terraform init to initialize a working directory (.terraform) containing configuration files and installed plugins for required providers.</p><p>4. Once the Terraform configuration is set up, we can run terraform apply to create the necessary objects based on the config files. And that’s it! You should have a ClickHouse server running.</p><p>5. After the ClickHouse deployment is successfully provisioned, we can verify its status and access the ClickHouse service. Run kubectl get pods -n {your_namespace}</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/608/1*cG-tZBtf1lGsOu2wnXDNzg.png" /></figure><p>Additionally, you can customize the ClickHouse deployment further by modifying the Terraform configuration. This includes adjusting resource limits, enabling high availability, or adding additional configurations specific to your use case.</p><p>In conclusion, deploying ClickHouse on a Kubernetes cluster using Terraform provides a scalable and manageable solution for analytical database management. With the flexibility of Kubernetes and the infrastructure-as-code approach of Terraform, you can easily provision and manage ClickHouse resources on yout K8s cluster.</p><blockquote><em>You can use </em><a href="https://opentofu.org/"><em>OpenTofu</em></a><em> as an alternative to Terraform, which is open source and community driven.</em></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d4e2234c27af" width="1" height="1" alt=""><hr><p><a href="https://towardsdev.com/deploying-clickhouse-on-a-kubernetes-cluster-using-terraform-d4e2234c27af">Deploying ClickHouse on a Kubernetes cluster using Terraform</a> was originally published in <a href="https://towardsdev.com/">Towards Dev</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>