kandi background
Explore Kits

pinot | Apache Pinot - A realtime distributed OLAP datastore

 by   apache Java Version: release-0.10.0 License: Apache-2.0

 by   apache Java Version: release-0.10.0 License: Apache-2.0

Download this library from

kandi X-RAY | pinot Summary

pinot is a Java library typically used in Big Data, Kafka, Spark, Hadoop applications. pinot has no bugs, it has no vulnerabilities, it has build file available, it has a Permissive License and it has medium support. You can download it from GitHub.
Apache Pinot is a real-time distributed OLAP datastore, built to deliver scalable real-time analytics with low latency. It can ingest from batch data sources (such as Hadoop HDFS, Amazon S3, Azure ADLS, Google Cloud Storage) as well as stream data sources (such as Apache Kafka). Pinot was built by engineers at LinkedIn and Uber and is designed to scale up and out with no upper bound. Performance always remains constant based on the size of your cluster and an expected query per second (QPS) threshold. For getting started guides, deployment recipes, tutorials, and more, please visit our project documentation at https://docs.pinot.apache.org.
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • pinot has a medium active ecosystem.
  • It has 3910 star(s) with 872 fork(s). There are 235 watchers for this library.
  • There were 1 major release(s) in the last 6 months.
  • There are 771 open issues and 608 have been closed. On average issues are closed in 49 days. There are 112 open pull requests and 0 closed requests.
  • It has a neutral sentiment in the developer community.
  • The latest version of pinot is release-0.10.0
pinot Support
Best in #Java
Average in #Java
pinot Support
Best in #Java
Average in #Java

quality kandi Quality

  • pinot has no bugs reported.
pinot Quality
Best in #Java
Average in #Java
pinot Quality
Best in #Java
Average in #Java

securitySecurity

  • pinot has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
pinot Security
Best in #Java
Average in #Java
pinot Security
Best in #Java
Average in #Java

license License

  • pinot is licensed under the Apache-2.0 License. This license is Permissive.
  • Permissive licenses have the least restrictions, and you can use them in most projects.
pinot License
Best in #Java
Average in #Java
pinot License
Best in #Java
Average in #Java

buildReuse

  • pinot releases are available to install and integrate.
  • Build file is available. You can build the component from source.
  • Installation instructions are not available. Examples and code snippets are available.
pinot Reuse
Best in #Java
Average in #Java
pinot Reuse
Best in #Java
Average in #Java
Top functions reviewed by kandi - BETA

kandi has reviewed pinot and discovered the below as its top functions. This is intended to give you an instant insight into pinot implemented functionality, and help decide if they suit your requirements.

  • Handle SQL request .
  • Populates the result array .
  • Runs the query strategy .
  • Internal method for parsing top level predicate pairs .
  • Reduces the response to a broker .
  • Assigns all realtime segments to ideal state .
  • Optimize the date time conversion function .
  • Calculates the result metadata .
  • Validates the indexing configuration .
  • Creates the indices for a derived column .

pinot Key Features

Column-oriented: a column-oriented database with various compression schemes such as Run Length, Fixed Bit Length.

Pluggable indexing: pluggable indexing technologies Sorted Index, Bitmap Index, Inverted Index.

Query optimization: ability to optimize query/execution plan based on query and segment metadata.

Stream and batch ingest: near real time ingestion from streams and batch ingestion from Hadoop.

Query with SQL: SQL-like language that supports selection, aggregation, filtering, group by, order by, distinct queries on data.

Upsert during real-time ingestion: update the data at-scale with consistency

Multi-valued fields: support for multi-valued fields, allowing you to query fields as comma separated values.

Cloud-native on Kubernetes: Helm chart provides a horizontally scalable and fault-tolerant clustered deployment that is easy to manage using Kubernetes.

When should I use Pinot?

copy iconCopydownload iconDownload
SELECT sum(clicks), sum(impressions) FROM AdAnalyticsTable
  WHERE
       ((daysSinceEpoch >= 17849 AND daysSinceEpoch <= 17856)) AND
       accountId IN (123456789)
  GROUP BY
       daysSinceEpoch TOP 100

Building Pinot

copy iconCopydownload iconDownload
# Clone a repo
$ git clone https://github.com/apache/pinot.git
$ cd pinot

# Build Pinot
$ mvn clean install -DskipTests -Pbin-dist

# Run the Quick Demo
$ cd build/
$ bin/quick-start-batch.sh

merge grouped results into one document in Mongo

copy iconCopydownload iconDownload
 {
  $group: {
  _id: "tmp",
   array: {
    $push: "$$ROOT"
   }
  }
}
-----------------------
[
  {
    '$match': {
      'publicId': 'S23DCL'
    }
  }, {
    '$project': {
      'scores': '$scores', 
      'publicId': '$publicId', 
      'title': '$title', 
      'flights': '$flights'
    }
  }, {
    '$unwind': '$scores'
  }, {
    '$unwind': '$scores.scores'
  }, {
    '$lookup': {
      'from': 'collection', 
      'let': {
        'wId': '$scores.scores.wineId', 
        'tastingId': '$_id'
      }, 
      'pipeline': [
        {
          '$unwind': '$flights'
        }, {
          '$unwind': {
            'path': '$flights.wines', 
            'includeArrayIndex': 'index'
          }
        }, {
          '$match': {
            '$expr': {
              '$and': [
                {
                  '$eq': [
                    '$flights.wines.id', '$$wId'
                  ]
                }, {
                  '$eq': [
                    '$_id', '$$tastingId'
                  ]
                }
              ]
            }
          }
        }, {
          '$project': {
            '_id': 0, 
            'name': '$flights.wines.name', 
            'flight': '$flights.name', 
            'wineId': '$flights.wines.id', 
            'index': '$index'
          }
        }
      ], 
      'as': 'wine'
    }
  }, {
    '$addFields': {
      'scores.title': '$title'
    }
  }, {
    '$replaceRoot': {
      'newRoot': {
        '$mergeObjects': [
          {
            '_id': '$_id', 
            'scores': '$scores'
          }, {
            'wine': {
              '$arrayElemAt': [
                '$wine', 0
              ]
            }
          }
        ]
      }
    }
  }, {
    '$group': {
      '_id': '$wine', 
      'title': {
        '$last': '$scores.title'
      }, 
      'avg': {
        '$avg': '$scores.scores.score'
      }, 
      'min': {
        '$min': '$scores.scores.score'
      }, 
      'max': {
        '$max': '$scores.scores.score'
      }, 
      'stddev': {
        '$stdDevPop': '$scores.scores.score'
      }, 
      'tmp': {
        '$max': 1
      }, 
      'scores': {
        '$addToSet': '$scores'
      }
    }
  }
]

Unset Array based on key value

copy iconCopydownload iconDownload
unset($_product[$month]);
-----------------------
$json = $json = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
$json = '[
    {
        "id": "1388",
        "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
        "image": "linkurl",
        "month": "June 2019",
        "activationdate": "2019-06-01",
        "wine1": "2014 Kate Hill Pinot Noir",
        "wine2": "2014 Pressing Matters Pinot Noir"
    },
    {
        "id": "8421",
        "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
        "image": "linkurl",
        "month": "December 2021",
        "activationdate": "2021-12-03",
        "wine1": "Apsley Gorge Pinot Noir 2018",
        "wine2": "Milton Pinot Noir 2019"
    }
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
print_r($products);
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
print_r($products);
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

    [1] => stdClass Object
        (
            [id] => 8421
            [name] => December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38
            [image] => linkurl
            [month] => December 2021
            [activationdate] => 2021-12-03
            [wine1] => Apsley Gorge Pinot Noir 2018
            [wine2] => Milton Pinot Noir 2019
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

)
-----------------------
$json = $json = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
$json = '[
    {
        "id": "1388",
        "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
        "image": "linkurl",
        "month": "June 2019",
        "activationdate": "2019-06-01",
        "wine1": "2014 Kate Hill Pinot Noir",
        "wine2": "2014 Pressing Matters Pinot Noir"
    },
    {
        "id": "8421",
        "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
        "image": "linkurl",
        "month": "December 2021",
        "activationdate": "2021-12-03",
        "wine1": "Apsley Gorge Pinot Noir 2018",
        "wine2": "Milton Pinot Noir 2019"
    }
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
print_r($products);
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
print_r($products);
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

    [1] => stdClass Object
        (
            [id] => 8421
            [name] => December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38
            [image] => linkurl
            [month] => December 2021
            [activationdate] => 2021-12-03
            [wine1] => Apsley Gorge Pinot Noir 2018
            [wine2] => Milton Pinot Noir 2019
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

)
-----------------------
$json = $json = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
$json = '[
    {
        "id": "1388",
        "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
        "image": "linkurl",
        "month": "June 2019",
        "activationdate": "2019-06-01",
        "wine1": "2014 Kate Hill Pinot Noir",
        "wine2": "2014 Pressing Matters Pinot Noir"
    },
    {
        "id": "8421",
        "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
        "image": "linkurl",
        "month": "December 2021",
        "activationdate": "2021-12-03",
        "wine1": "Apsley Gorge Pinot Noir 2018",
        "wine2": "Milton Pinot Noir 2019"
    }
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
print_r($products);
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
print_r($products);
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

    [1] => stdClass Object
        (
            [id] => 8421
            [name] => December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38
            [image] => linkurl
            [month] => December 2021
            [activationdate] => 2021-12-03
            [wine1] => Apsley Gorge Pinot Noir 2018
            [wine2] => Milton Pinot Noir 2019
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

)
-----------------------
$json = $json = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
$json = '[
    {
        "id": "1388",
        "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
        "image": "linkurl",
        "month": "June 2019",
        "activationdate": "2019-06-01",
        "wine1": "2014 Kate Hill Pinot Noir",
        "wine2": "2014 Pressing Matters Pinot Noir"
    },
    {
        "id": "8421",
        "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
        "image": "linkurl",
        "month": "December 2021",
        "activationdate": "2021-12-03",
        "wine1": "Apsley Gorge Pinot Noir 2018",
        "wine2": "Milton Pinot Noir 2019"
    }
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
print_r($products);
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
print_r($products);
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

    [1] => stdClass Object
        (
            [id] => 8421
            [name] => December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38
            [image] => linkurl
            [month] => December 2021
            [activationdate] => 2021-12-03
            [wine1] => Apsley Gorge Pinot Noir 2018
            [wine2] => Milton Pinot Noir 2019
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

)
-----------------------
$json = $json = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
$json = '[
    {
        "id": "1388",
        "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
        "image": "linkurl",
        "month": "June 2019",
        "activationdate": "2019-06-01",
        "wine1": "2014 Kate Hill Pinot Noir",
        "wine2": "2014 Pressing Matters Pinot Noir"
    },
    {
        "id": "8421",
        "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
        "image": "linkurl",
        "month": "December 2021",
        "activationdate": "2021-12-03",
        "wine1": "Apsley Gorge Pinot Noir 2018",
        "wine2": "Milton Pinot Noir 2019"
    }
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
print_r($products);
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
print_r($products);
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

    [1] => stdClass Object
        (
            [id] => 8421
            [name] => December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38
            [image] => linkurl
            [month] => December 2021
            [activationdate] => 2021-12-03
            [wine1] => Apsley Gorge Pinot Noir 2018
            [wine2] => Milton Pinot Noir 2019
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

)
-----------------------
$json = $json = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
$json = '[
    {
        "id": "1388",
        "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
        "image": "linkurl",
        "month": "June 2019",
        "activationdate": "2019-06-01",
        "wine1": "2014 Kate Hill Pinot Noir",
        "wine2": "2014 Pressing Matters Pinot Noir"
    },
    {
        "id": "8421",
        "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
        "image": "linkurl",
        "month": "December 2021",
        "activationdate": "2021-12-03",
        "wine1": "Apsley Gorge Pinot Noir 2018",
        "wine2": "Milton Pinot Noir 2019"
    }
]';
$products = json_decode($json);
$current_date = strtotime(date('Y-m-d'));
print_r($products);
foreach ($products as $month => $product) {
    if(strtotime($product->activationdate) > $current_date) { // Date comparison
        unset($products[$month]); // Here you made a mistake
    }
}
print_r($products);
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

    [1] => stdClass Object
        (
            [id] => 8421
            [name] => December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38
            [image] => linkurl
            [month] => December 2021
            [activationdate] => 2021-12-03
            [wine1] => Apsley Gorge Pinot Noir 2018
            [wine2] => Milton Pinot Noir 2019
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1388
            [name] => June 2019 -  2014 Kate Hill & 2014 Pressing Matters
            [image] => linkurl
            [month] => June 2019
            [activationdate] => 2019-06-01
            [wine1] => 2014 Kate Hill Pinot Noir
            [wine2] => 2014 Pressing Matters Pinot Noir
        )

)
-----------------------
foreach($_products as $k=>$v ) 

if( $v->activationdate>$date_now) unset($_products[$k]);
<?php

$s = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';

$p=json_decode($s);

$date_now = date('Y-m-d'); //get today's date with format e.g 2021-01-02

foreach($p as $k=>$v ) if( $v->activationdate>$date_now) unset($p[$k]);

var_dump( $p);
?>
-----------------------
foreach($_products as $k=>$v ) 

if( $v->activationdate>$date_now) unset($_products[$k]);
<?php

$s = '[
{
    "id": "1388",
    "name": "June 2019 -  2014 Kate Hill & 2014 Pressing Matters",
    "image": "linkurl",
    "month": "June 2019",
    "activationdate": "2019-06-01",
    "wine1": "2014 Kate Hill Pinot Noir",
    "wine2": "2014 Pressing Matters Pinot Noir"
},
{
    "id": "8421",
    "name": "December 2021 Releases: Apsley Gorge Pinot Noir 2018 $65 & Milton Pinot Noir 2019 $38",
    "image": "linkurl",
    "month": "December 2021",
    "activationdate": "2021-12-03",
    "wine1": "Apsley Gorge Pinot Noir 2018",
    "wine2": "Milton Pinot Noir 2019"
}
]';

$p=json_decode($s);

$date_now = date('Y-m-d'); //get today's date with format e.g 2021-01-02

foreach($p as $k=>$v ) if( $v->activationdate>$date_now) unset($p[$k]);

var_dump( $p);
?>

Removing everything between 2 strings with Google sheets RE2

copy iconCopydownload iconDownload
(?i), .*?[CM]?L
", .*
-----------------------
(?i), .*?[CM]?L
", .*

Why am I not being able to insert values into my SQL database?

copy iconCopydownload iconDownload
 VARCHAR(15) NULL

Is there a way to fill a column of a dataframe with values from a list, grouped by each individual item - Python

copy iconCopydownload iconDownload
# Split ambiguous column.
df['region'] = df['Region/Variety'].copy()
df['variety'] = df['Region/Variety'].copy()
df = df.drop('Region/Variety', axis=1)

# Shadow invalid regions.
idx_regions = df['region'].isin(regions)
df.loc[~idx_regions,'region'] = None
# fill up missing regions
df['region'] = df['region'].ffill()

# Drop superfluous rows.
df = df.loc[df['variety'] != df['region']]
-----------------------
df["Region"] = df["Region/Variety"].where(df["Region/Variety"].isin(regions)).ffill()
df = (
    df[df["Region/Variety"] != df["Region"]]
    .reset_index().rename(columns={"Region/Variety": "Variety"})
)[["Region", "Variety", "2011"]]
df:
   Region/Variety   2011
0  Adelaide Hills   3452
1      Chardonnay  26357
2    Pinot Grigio   7876
3  Barossa Valley   7368
4      Chardonnay   8787
           Region       Variety   2011
0  Adelaide Hills    Chardonnay  26357
1  Adelaide Hills  Pinot Grigio   7876
2  Barossa Valley    Chardonnay   8787
-----------------------
df["Region"] = df["Region/Variety"].where(df["Region/Variety"].isin(regions)).ffill()
df = (
    df[df["Region/Variety"] != df["Region"]]
    .reset_index().rename(columns={"Region/Variety": "Variety"})
)[["Region", "Variety", "2011"]]
df:
   Region/Variety   2011
0  Adelaide Hills   3452
1      Chardonnay  26357
2    Pinot Grigio   7876
3  Barossa Valley   7368
4      Chardonnay   8787
           Region       Variety   2011
0  Adelaide Hills    Chardonnay  26357
1  Adelaide Hills  Pinot Grigio   7876
2  Barossa Valley    Chardonnay   8787
-----------------------
df["Region"] = df["Region/Variety"].where(df["Region/Variety"].isin(regions)).ffill()
df = (
    df[df["Region/Variety"] != df["Region"]]
    .reset_index().rename(columns={"Region/Variety": "Variety"})
)[["Region", "Variety", "2011"]]
df:
   Region/Variety   2011
0  Adelaide Hills   3452
1      Chardonnay  26357
2    Pinot Grigio   7876
3  Barossa Valley   7368
4      Chardonnay   8787
           Region       Variety   2011
0  Adelaide Hills    Chardonnay  26357
1  Adelaide Hills  Pinot Grigio   7876
2  Barossa Valley    Chardonnay   8787

How to print a dictionary with multiple values without brackets?

copy iconCopydownload iconDownload
class Wine:
    def __init__(self, key):
        self.a = {}
        self.key = key
        self.value = []
        self.a.setdefault(key, [])

    def addYear(self, value):
        self.a[self.key].insert(1, value)

    def addProd(self, value):
        self.a[self.key].insert(2, value)
    
    def addCountry(self, value):
        self.a[self.key].insert(3, value)
    
    def addPrice(self, value):
        self.a[self.key].insert(4, value)
    
    def __str__(self):
        for key, value in self.a.items():
            value = ', '.join(map(str, value))
            print("{}, {}".format(key, value))
        
b = Wine("Bread and Butter Pinot Noir")
b.addYear("2017")
b.addProd("Sonoma County")
b.addCountry("USA")
b.addPrice("30 USD")
b.__str__()

Not able to make Apache Superset connect to Presto DB (this PrestoDB is connected to Apache Pinot)

copy iconCopydownload iconDownload
presto://{continer_ip}:8080/pinot
presto://{host_id}:8080/pinot

Pinot nested json ingestion

copy iconCopydownload iconDownload
{
  "metricFieldSpecs": [],
  "dimensionFieldSpecs": [
    {
      "dataType": "STRING",
      "name": "name"
    },
    {
      "dataType": "LONG",
      "name": "age"
    },
    {
      "dataType": "STRING",
      "name": "subjects_str"
    },
    {
      "dataType": "STRING",
      "name": "subjects_name",
      "singleValueField": false
    },
    {
      "dataType": "STRING",
      "name": "subjects_grade",
      "singleValueField": false
    }
  ],
  "dateTimeFieldSpecs": [],
  "schemaName": "myTable"
}
{
    "tableName": "myTable",
    "tableType": "OFFLINE",
    "segmentsConfig": {
        "segmentPushType": "APPEND",
        "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
        "schemaName": "myTable",
        "replication": "1"
    },
    "tenants": {},
    "tableIndexConfig": {
        "loadMode": "MMAP",
        "invertedIndexColumns": [],
        "noDictionaryColumns": [
            "subjects_str"
        ],
        "jsonIndexColumns": [
            "subjects_str"
        ]
    },
    "metadata": {
        "customConfigs": {}
    },
    "ingestionConfig": {
        "batchIngestionConfig": {
            "segmentIngestionType": "APPEND",
            "segmentIngestionFrequency": "DAILY",
            "batchConfigMaps": [],
            "segmentNameSpec": {},
            "pushSpec": {}
        },
        "transformConfigs": [
            {
                "columnName": "subjects_str",
                "transformFunction": "jsonFormat(subjects)"
            },
            {
                "columnName": "subjects_name",
                "transformFunction": "jsonPathArray(subjects, '$.[*].name')"
            },
            {
                "columnName": "subjects_grade",
                "transformFunction": "jsonPathArray(subjects, '$.[*].grade')"
            }
        ]
    }
}
select age, subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
select age, json_extract_scalar(subjects_str, '$.[*].grade', 'STRING') as subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
-----------------------
{
  "metricFieldSpecs": [],
  "dimensionFieldSpecs": [
    {
      "dataType": "STRING",
      "name": "name"
    },
    {
      "dataType": "LONG",
      "name": "age"
    },
    {
      "dataType": "STRING",
      "name": "subjects_str"
    },
    {
      "dataType": "STRING",
      "name": "subjects_name",
      "singleValueField": false
    },
    {
      "dataType": "STRING",
      "name": "subjects_grade",
      "singleValueField": false
    }
  ],
  "dateTimeFieldSpecs": [],
  "schemaName": "myTable"
}
{
    "tableName": "myTable",
    "tableType": "OFFLINE",
    "segmentsConfig": {
        "segmentPushType": "APPEND",
        "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
        "schemaName": "myTable",
        "replication": "1"
    },
    "tenants": {},
    "tableIndexConfig": {
        "loadMode": "MMAP",
        "invertedIndexColumns": [],
        "noDictionaryColumns": [
            "subjects_str"
        ],
        "jsonIndexColumns": [
            "subjects_str"
        ]
    },
    "metadata": {
        "customConfigs": {}
    },
    "ingestionConfig": {
        "batchIngestionConfig": {
            "segmentIngestionType": "APPEND",
            "segmentIngestionFrequency": "DAILY",
            "batchConfigMaps": [],
            "segmentNameSpec": {},
            "pushSpec": {}
        },
        "transformConfigs": [
            {
                "columnName": "subjects_str",
                "transformFunction": "jsonFormat(subjects)"
            },
            {
                "columnName": "subjects_name",
                "transformFunction": "jsonPathArray(subjects, '$.[*].name')"
            },
            {
                "columnName": "subjects_grade",
                "transformFunction": "jsonPathArray(subjects, '$.[*].grade')"
            }
        ]
    }
}
select age, subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
select age, json_extract_scalar(subjects_str, '$.[*].grade', 'STRING') as subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
-----------------------
{
  "metricFieldSpecs": [],
  "dimensionFieldSpecs": [
    {
      "dataType": "STRING",
      "name": "name"
    },
    {
      "dataType": "LONG",
      "name": "age"
    },
    {
      "dataType": "STRING",
      "name": "subjects_str"
    },
    {
      "dataType": "STRING",
      "name": "subjects_name",
      "singleValueField": false
    },
    {
      "dataType": "STRING",
      "name": "subjects_grade",
      "singleValueField": false
    }
  ],
  "dateTimeFieldSpecs": [],
  "schemaName": "myTable"
}
{
    "tableName": "myTable",
    "tableType": "OFFLINE",
    "segmentsConfig": {
        "segmentPushType": "APPEND",
        "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
        "schemaName": "myTable",
        "replication": "1"
    },
    "tenants": {},
    "tableIndexConfig": {
        "loadMode": "MMAP",
        "invertedIndexColumns": [],
        "noDictionaryColumns": [
            "subjects_str"
        ],
        "jsonIndexColumns": [
            "subjects_str"
        ]
    },
    "metadata": {
        "customConfigs": {}
    },
    "ingestionConfig": {
        "batchIngestionConfig": {
            "segmentIngestionType": "APPEND",
            "segmentIngestionFrequency": "DAILY",
            "batchConfigMaps": [],
            "segmentNameSpec": {},
            "pushSpec": {}
        },
        "transformConfigs": [
            {
                "columnName": "subjects_str",
                "transformFunction": "jsonFormat(subjects)"
            },
            {
                "columnName": "subjects_name",
                "transformFunction": "jsonPathArray(subjects, '$.[*].name')"
            },
            {
                "columnName": "subjects_grade",
                "transformFunction": "jsonPathArray(subjects, '$.[*].grade')"
            }
        ]
    }
}
select age, subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
select age, json_extract_scalar(subjects_str, '$.[*].grade', 'STRING') as subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
-----------------------
{
  "metricFieldSpecs": [],
  "dimensionFieldSpecs": [
    {
      "dataType": "STRING",
      "name": "name"
    },
    {
      "dataType": "LONG",
      "name": "age"
    },
    {
      "dataType": "STRING",
      "name": "subjects_str"
    },
    {
      "dataType": "STRING",
      "name": "subjects_name",
      "singleValueField": false
    },
    {
      "dataType": "STRING",
      "name": "subjects_grade",
      "singleValueField": false
    }
  ],
  "dateTimeFieldSpecs": [],
  "schemaName": "myTable"
}
{
    "tableName": "myTable",
    "tableType": "OFFLINE",
    "segmentsConfig": {
        "segmentPushType": "APPEND",
        "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
        "schemaName": "myTable",
        "replication": "1"
    },
    "tenants": {},
    "tableIndexConfig": {
        "loadMode": "MMAP",
        "invertedIndexColumns": [],
        "noDictionaryColumns": [
            "subjects_str"
        ],
        "jsonIndexColumns": [
            "subjects_str"
        ]
    },
    "metadata": {
        "customConfigs": {}
    },
    "ingestionConfig": {
        "batchIngestionConfig": {
            "segmentIngestionType": "APPEND",
            "segmentIngestionFrequency": "DAILY",
            "batchConfigMaps": [],
            "segmentNameSpec": {},
            "pushSpec": {}
        },
        "transformConfigs": [
            {
                "columnName": "subjects_str",
                "transformFunction": "jsonFormat(subjects)"
            },
            {
                "columnName": "subjects_name",
                "transformFunction": "jsonPathArray(subjects, '$.[*].name')"
            },
            {
                "columnName": "subjects_grade",
                "transformFunction": "jsonPathArray(subjects, '$.[*].grade')"
            }
        ]
    }
}
select age, subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade
select age, json_extract_scalar(subjects_str, '$.[*].grade', 'STRING') as subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade

How to loop through xml table row and access the column values by attribute names

copy iconCopydownload iconDownload
$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);

// iterate the 'POS:Plus' table element(s)
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:Plus"]') as $table) {
    $records = [];
    $tableName = $table->getAttribute('name');
    // for each row child element
    foreach ($xpath->evaluate('row', $table) as $row) {
        // fetch row values
        $records[] = [
          'Id' => $row->getAttribute('id'),
          'LongDescription' => $xpath->evaluate('string(col[@name="LongDescription"])', $row),
          'TillDescription' => $xpath->evaluate('string(col[@name="TillDescription"])', $row),
          'TouchscreenDescription' => $xpath->evaluate('string(col[@name="TouchscreenDescription"])', $row),
        ];
    }
    var_dump($tableName, $records);
}
// next table
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:PLUPrices"]') as $table) {
    // ...
}
// ...
string(8) "POS:Plus"
array(1) {
  [0]=>
  array(4) {
    ["Id"]=>
    string(1) "6"
    ["LongDescription"]=>
    string(13) "TeTera Pinot "
    ["TillDescription"]=>
    string(12) "TeTera Pinot"
    ["TouchscreenDescription"]=>
    string(13) "TeTera Peroni"
  }
}
$xml = <<<'XML'
<?xml version="1.0" ?>
<atp xmlns="http://xml.alamte.com/atp">
  <transaction> 
    <table name="POS:Plus">...</table>
  </transaction>
</atp>
XML;

$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);
$xpath->registerNamespace('atp', 'http://xml.alamte.com/atp');

// iterate the 'POS:Plus' table elements
foreach ($xpath->evaluate('/atp:atp/atp:transaction/atp:table[@name="POS:Plus"]') as $table) {
    $tableName = $table->getAttribute('name');
    var_dump($tableName);
}
string(8) "POS:Plus"
-----------------------
$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);

// iterate the 'POS:Plus' table element(s)
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:Plus"]') as $table) {
    $records = [];
    $tableName = $table->getAttribute('name');
    // for each row child element
    foreach ($xpath->evaluate('row', $table) as $row) {
        // fetch row values
        $records[] = [
          'Id' => $row->getAttribute('id'),
          'LongDescription' => $xpath->evaluate('string(col[@name="LongDescription"])', $row),
          'TillDescription' => $xpath->evaluate('string(col[@name="TillDescription"])', $row),
          'TouchscreenDescription' => $xpath->evaluate('string(col[@name="TouchscreenDescription"])', $row),
        ];
    }
    var_dump($tableName, $records);
}
// next table
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:PLUPrices"]') as $table) {
    // ...
}
// ...
string(8) "POS:Plus"
array(1) {
  [0]=>
  array(4) {
    ["Id"]=>
    string(1) "6"
    ["LongDescription"]=>
    string(13) "TeTera Pinot "
    ["TillDescription"]=>
    string(12) "TeTera Pinot"
    ["TouchscreenDescription"]=>
    string(13) "TeTera Peroni"
  }
}
$xml = <<<'XML'
<?xml version="1.0" ?>
<atp xmlns="http://xml.alamte.com/atp">
  <transaction> 
    <table name="POS:Plus">...</table>
  </transaction>
</atp>
XML;

$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);
$xpath->registerNamespace('atp', 'http://xml.alamte.com/atp');

// iterate the 'POS:Plus' table elements
foreach ($xpath->evaluate('/atp:atp/atp:transaction/atp:table[@name="POS:Plus"]') as $table) {
    $tableName = $table->getAttribute('name');
    var_dump($tableName);
}
string(8) "POS:Plus"
-----------------------
$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);

// iterate the 'POS:Plus' table element(s)
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:Plus"]') as $table) {
    $records = [];
    $tableName = $table->getAttribute('name');
    // for each row child element
    foreach ($xpath->evaluate('row', $table) as $row) {
        // fetch row values
        $records[] = [
          'Id' => $row->getAttribute('id'),
          'LongDescription' => $xpath->evaluate('string(col[@name="LongDescription"])', $row),
          'TillDescription' => $xpath->evaluate('string(col[@name="TillDescription"])', $row),
          'TouchscreenDescription' => $xpath->evaluate('string(col[@name="TouchscreenDescription"])', $row),
        ];
    }
    var_dump($tableName, $records);
}
// next table
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:PLUPrices"]') as $table) {
    // ...
}
// ...
string(8) "POS:Plus"
array(1) {
  [0]=>
  array(4) {
    ["Id"]=>
    string(1) "6"
    ["LongDescription"]=>
    string(13) "TeTera Pinot "
    ["TillDescription"]=>
    string(12) "TeTera Pinot"
    ["TouchscreenDescription"]=>
    string(13) "TeTera Peroni"
  }
}
$xml = <<<'XML'
<?xml version="1.0" ?>
<atp xmlns="http://xml.alamte.com/atp">
  <transaction> 
    <table name="POS:Plus">...</table>
  </transaction>
</atp>
XML;

$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);
$xpath->registerNamespace('atp', 'http://xml.alamte.com/atp');

// iterate the 'POS:Plus' table elements
foreach ($xpath->evaluate('/atp:atp/atp:transaction/atp:table[@name="POS:Plus"]') as $table) {
    $tableName = $table->getAttribute('name');
    var_dump($tableName);
}
string(8) "POS:Plus"
-----------------------
$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);

// iterate the 'POS:Plus' table element(s)
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:Plus"]') as $table) {
    $records = [];
    $tableName = $table->getAttribute('name');
    // for each row child element
    foreach ($xpath->evaluate('row', $table) as $row) {
        // fetch row values
        $records[] = [
          'Id' => $row->getAttribute('id'),
          'LongDescription' => $xpath->evaluate('string(col[@name="LongDescription"])', $row),
          'TillDescription' => $xpath->evaluate('string(col[@name="TillDescription"])', $row),
          'TouchscreenDescription' => $xpath->evaluate('string(col[@name="TouchscreenDescription"])', $row),
        ];
    }
    var_dump($tableName, $records);
}
// next table
foreach ($xpath->evaluate('/atp/transaction/table[@name="POS:PLUPrices"]') as $table) {
    // ...
}
// ...
string(8) "POS:Plus"
array(1) {
  [0]=>
  array(4) {
    ["Id"]=>
    string(1) "6"
    ["LongDescription"]=>
    string(13) "TeTera Pinot "
    ["TillDescription"]=>
    string(12) "TeTera Pinot"
    ["TouchscreenDescription"]=>
    string(13) "TeTera Peroni"
  }
}
$xml = <<<'XML'
<?xml version="1.0" ?>
<atp xmlns="http://xml.alamte.com/atp">
  <transaction> 
    <table name="POS:Plus">...</table>
  </transaction>
</atp>
XML;

$document = new DOMDocument();
$document->loadXML($xml);
$xpath = new DOMXpath($document);
$xpath->registerNamespace('atp', 'http://xml.alamte.com/atp');

// iterate the 'POS:Plus' table elements
foreach ($xpath->evaluate('/atp:atp/atp:transaction/atp:table[@name="POS:Plus"]') as $table) {
    $tableName = $table->getAttribute('name');
    var_dump($tableName);
}
string(8) "POS:Plus"

Making a ComboBox Selection Control Visibility/Non Visibility of Shapes Powerpoint VBA

copy iconCopydownload iconDownload
Private Sub ComboBox1_GotFocus()
    If ComboBox1.ListCount = 0 Then AddDropDownItems
End Sub

Sub AddDropDownItems()
    ComboBox1.AddItem "2018 Pinot Noir"
    ComboBox1.AddItem "2019 Pinot Noir"
    ComboBox1.AddItem "2020 Pinot Noir"
    ComboBox1.ListRows = 3
End Sub

Private Sub ComboBox1_Change()
    Dim imgPinot As Shape
    Dim imgPinot2 As Shape
    Dim imgPinot3 As Shape

    Select Case ComboBox1.ListIndex
        Case 0
            With ActivePresentation.Slides(1)
                .Shapes("imgPinot").Visible = True
                .Shapes("imgPinot2").Visible = False
                .Shapes("imgPinot3").Visible = False
            End With
        Case 1
            With ActivePresentation.Slides(1)
                .Shapes("imgPinot").Visible = False
                .Shapes("imgPinot2").Visible = True
                .Shapes("imgPinot3").Visible = False
            End With
        Case 2
            With ActivePresentation.Slides(1)
                .Shapes("imgPinot").Visible = False
                .Shapes("imgPinot2").Visible = False
                .Shapes("imgPinot3").Visible = True
            End With
    End Select
End Sub

-----------------------
Option Explicit

Private Sub ComboBox1_GotFocus()
    If ComboBox1.ListCount = 0 Then AddDropDownItems
End Sub

Sub AddDropDownItems()
    ComboBox1.AddItem "2018 Pinot Noir"
    ComboBox1.AddItem "2019 Pinot Noir"
    ComboBox1.AddItem "2020 Pinot Noir"
    ComboBox1.ListRows = 3
End Sub

Sub ComboBox1_Change()
    Dim sel, n As Long, cb As ComboBox, nm
    
    Set cb = Me.ComboBox1
    sel = cb.Value                         'selected item
    'loop over list 
    For n = 1 To cb.ListCount
        nm = cb.List(n - 1)                'list entry
        Me.Shapes(nm).Visible = (nm = sel) 'show only if entry matches selection
    Next n
End Sub

Community Discussions

Trending Discussions on pinot
  • merge grouped results into one document in Mongo
  • Unset Array based on key value
  • Removing everything between 2 strings with Google sheets RE2
  • Why am I not being able to insert values into my SQL database?
  • Is there a way to fill a column of a dataframe with values from a list, grouped by each individual item - Python
  • Error Code PINOT_UNABLE_TO_FIND_BROKER :No valid brokers found
  • How to print a dictionary with multiple values without brackets?
  • Not able to make Apache Superset connect to Presto DB (this PrestoDB is connected to Apache Pinot)
  • How does Apache Pinot index data when compared to Elasticsearch?
  • Pinot nested json ingestion
Trending Discussions on pinot

QUESTION

merge grouped results into one document in Mongo

Asked 2022-Mar-22 at 07:46

I have a document that looks like this

  {
    "_id": {
      "$oid": "6187e5fa4ebcc2db6c86081f"
    },
    "publicId": "S23DCL",
    "flights": [
      {
        "name": "f1",
        "_id": {
          "$oid": "620026293fc350024da614dd"
        },
        "wines": [
          {
            "id": "1",
            "name": "wine1"
          },
          {
            "id": "2",
            "name": "wine2"
          }
        ]
      },
      {
        "name": "f2",
        "_id": {
          "$oid": "6200263c3fc350024da614de"
        },
        "wines": [
          {
            "id": "3",
            "name": "leVin"
          },
          {
            "id": "4",
            "name": "theWine"
          }
        ]
      }
    ],
    "title": "Pinot Noir 2022",
    "scores": [
      {
        "userId": "f8Dv",
        "userName": "gugus",
        "scores": [
          {
            "score": 95,
            "wineId": "1"
          },
          {
            "score": 88,
            "wineId": "2"
          }
        ]
      },
      {
        "userId": "yLjh",
        "userName": "test",
        "scores": [
          {
            "score": 92,
            "wineId": "1"
          },
          {
            "score": 87,
            "wineId": "2"
          }
        ]
      },
      {
        "userId": "B6em",
        "userName": "jklsdf",
        "scores": [
          {
            "score": 88,
            "wineId": "1"
          },
          {
            "score": 90,
            "wineId": "2"
          },
          {
            "score": 92,
            "wineId": "3"
          },
          {
            "score": 86,
            "wineId": "4"
          }
        ],
        "isFinished": false
      }
    ],
  }

I if it's too long for the question I can shorten it.
The whole document and query can also be found in mongoplayground

What I want to do is to create a scoreboard for all wines, meaning caluclating avg etc. for every wine, while still having just one document.

Something like this

{
  "title": "Pinot Noir 2022"
  "results": [
    {
       "wine": {
          "flightName": "f1",
           "wineIndex": 1,
           "name": "wine1",
           "wineId": "1"
        },
        "avg": 90,
        "scores": [
           {
        "scores": {
          "score": 87,
          "wineId": "1"
        },
        "userId": "yLjh",
        "userName": "test"
      },
        ] 
    }
   ]
}

I'm almost there but struggle with the part were I want a single result document after I grouped it.

the wineId within scores coresponds to the id in flights.wines

Here is what I have so far

db.collection.aggregate([
  {
    "$match": {
      "publicId": "S23DCL"
    }
  },
  {
    "$project": {
      "scores": "$scores",
      "publicId": "$publicId"
    }
  },
  {
    "$unwind": "$scores"
  },
  {
    "$unwind": "$scores.scores"
  },
  {
    $lookup: {
      from: "collection",
      let: {
        wId: "$scores.scores.wineId",
        "tastingId": "$_id"
      },
      pipeline: [
        {
          "$unwind": "$flights"
        },
        {
          "$unwind": {
            "path": "$flights.wines",
            "includeArrayIndex": "index"
          }
        },
        {
          $match: {
            $expr: {
              "$and": [
                {
                  "$eq": [
                    "$flights.wines.id",
                    "$$wId"
                  ]
                },
                {
                  "$eq": [
                    "$_id",
                    "$$tastingId"
                  ]
                }
              ]
            },
            
          }
        },
        {
          $project: {
            _id: 0,
            "name": "$flights.wines.name",
            "flight": "$flights.name",
            "wineId": "$flights.wines.id",
            "index": "$index"
          }
        }
      ],
      as: "wine"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          {
            "_id": "$_id",
            "scores": "$scores"
          },
          {
            "wine": {
              "$arrayElemAt": [
                "$wine",
                0
              ]
            }
          }
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$wine",
      "avg": {
        "$avg": "$scores.scores.score"
      },
      "min": {
        "$min": "$scores.scores.score"
      },
      "max": {
        "$max": "$scores.scores.score"
      },
      "stddev": {
        "$stdDevPop": "$scores.scores.score"
      },
      "tmp": {
        "$max": 1
      },
      "scores": {
        "$addToSet": "$scores"
      }
    }
  }
])

This gives me data as seen in results above. But data like "title" is missing.
Note the tmp field. I tried to add this dummy field and group on it in later stage.
Since there will always only be one group (because tmp is static).
Like

// query from abive
,{
 "$group": {
  "$_id": "tmp"
  // add my grouped results from before into an array
}
}

So how can I add my grouped results into an array, so I don't get (n) groups back but one documents where the grouped results are in an array?

Hope it's clear what I mean :)

ANSWER

Answered 2022-Mar-22 at 07:44

Maybe something like this:

 {
  $group: {
  _id: "tmp",
   array: {
    $push: "$$ROOT"
   }
  }
}

Source https://stackoverflow.com/questions/71567626

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install pinot

You can download it from GitHub.
You can use pinot like any standard Java library. Please include the the jar files in your classpath. You can also use any IDE and you can run and debug the pinot component as you would do with any other Java program. Best practice is to use a build tool that supports dependency management such as Maven or Gradle. For Maven installation, please refer maven.apache.org. For Gradle installation, please refer gradle.org .

Support

Ask questions on Apache Pinot SlackPlease join Apache Pinot mailing lists dev-subscribe@pinot.apache.org (subscribe to pinot-dev mailing list) dev@pinot.apache.org (posting to pinot-dev mailing list) users-subscribe@pinot.apache.org (subscribe to pinot-user mailing list) users@pinot.apache.org (posting to pinot-user mailing list)Apache Pinot Meetup Group: https://www.meetup.com/apache-pinot/

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Explore Related Topics

Share this Page

share link
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.