tutorials/suggestions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Constraint Suggestions Basic Tutorial\n",
    "\n",
    "This Jupyter notebook will give a basic tutorial on how to use PyDeequ's Constraint Suggestions module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession, Row, DataFrame\n",
    "import json\n",
    "import pandas as pd\n",
    "import sagemaker_pyspark\n",
    "\n",
    "import pydeequ\n",
    "\n",
    "classpath = \":\".join(sagemaker_pyspark.classpath_jars())\n",
    "\n",
    "spark = (SparkSession\n",
    "    .builder\n",
    "    .config(\"spark.driver.extraClassPath\", classpath)\n",
    "    .config(\"spark.jars.packages\", pydeequ.deequ_maven_coord)\n",
    "    .config(\"spark.jars.excludes\", pydeequ.f2j_maven_coord)\n",
    "    .getOrCreate())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We will be using the Amazon Product Reviews dataset -- specifically the Electronics subset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- marketplace: string (nullable = true)\n",
      " |-- customer_id: string (nullable = true)\n",
      " |-- review_id: string (nullable = true)\n",
      " |-- product_id: string (nullable = true)\n",
      " |-- product_parent: string (nullable = true)\n",
      " |-- product_title: string (nullable = true)\n",
      " |-- star_rating: integer (nullable = true)\n",
      " |-- helpful_votes: integer (nullable = true)\n",
      " |-- total_votes: integer (nullable = true)\n",
      " |-- vine: string (nullable = true)\n",
      " |-- verified_purchase: string (nullable = true)\n",
      " |-- review_headline: string (nullable = true)\n",
      " |-- review_body: string (nullable = true)\n",
      " |-- review_date: date (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = spark.read.parquet(\"s3a://amazon-reviews-pds/parquet/product_category=Electronics/\")\n",
    "\n",
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"constraint_suggestions\": [\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(review_id,None))\",\n",
      "      \"column_name\": \"review_id\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'review_id' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"review_id\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"UniquenessConstraint(Uniqueness(List(review_id),None))\",\n",
      "      \"column_name\": \"review_id\",\n",
      "      \"current_value\": \"ApproxDistinctness: 0.9647650802419017\",\n",
      "      \"description\": \"'review_id' is unique\",\n",
      "      \"suggesting_rule\": \"UniqueIfApproximatelyUniqueRule()\",\n",
      "      \"rule_description\": \"If the ratio of approximate num distinct values in a column is close to the number of records (within the error of the HLL sketch), we suggest a UNIQUE constraint\",\n",
      "      \"code_for_constraint\": \".isUnique(\\\"review_id\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(customer_id,None))\",\n",
      "      \"column_name\": \"customer_id\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'customer_id' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"customer_id\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('customer_id' has no negative values,customer_id >= 0,None))\",\n",
      "      \"column_name\": \"customer_id\",\n",
      "      \"current_value\": \"Minimum: 10005.0\",\n",
      "      \"description\": \"'customer_id' has no negative values\",\n",
      "      \"suggesting_rule\": \"NonNegativeNumbersRule()\",\n",
      "      \"rule_description\": \"If we see only non-negative numbers in a column, we suggest a corresponding constraint\",\n",
      "      \"code_for_constraint\": \".isNonNegative(\\\"customer_id\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"AnalysisBasedConstraint(DataType(customer_id,None),<function1>,Some(<function1>),None)\",\n",
      "      \"column_name\": \"customer_id\",\n",
      "      \"current_value\": \"DataType: Integral\",\n",
      "      \"description\": \"'customer_id' has type Integral\",\n",
      "      \"suggesting_rule\": \"RetainTypeRule()\",\n",
      "      \"rule_description\": \"If we detect a non-string type, we suggest a type constraint\",\n",
      "      \"code_for_constraint\": \".hasDataType(\\\"customer_id\\\", ConstrainableDataTypes.Integral)\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(review_date,None))\",\n",
      "      \"column_name\": \"review_date\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'review_date' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"review_date\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(helpful_votes,None))\",\n",
      "      \"column_name\": \"helpful_votes\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'helpful_votes' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"helpful_votes\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('helpful_votes' has no negative values,helpful_votes >= 0,None))\",\n",
      "      \"column_name\": \"helpful_votes\",\n",
      "      \"current_value\": \"Minimum: 0.0\",\n",
      "      \"description\": \"'helpful_votes' has no negative values\",\n",
      "      \"suggesting_rule\": \"NonNegativeNumbersRule()\",\n",
      "      \"rule_description\": \"If we see only non-negative numbers in a column, we suggest a corresponding constraint\",\n",
      "      \"code_for_constraint\": \".isNonNegative(\\\"helpful_votes\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(star_rating,None))\",\n",
      "      \"column_name\": \"star_rating\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'star_rating' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"star_rating\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('star_rating' has no negative values,star_rating >= 0,None))\",\n",
      "      \"column_name\": \"star_rating\",\n",
      "      \"current_value\": \"Minimum: 1.0\",\n",
      "      \"description\": \"'star_rating' has no negative values\",\n",
      "      \"suggesting_rule\": \"NonNegativeNumbersRule()\",\n",
      "      \"rule_description\": \"If we see only non-negative numbers in a column, we suggest a corresponding constraint\",\n",
      "      \"code_for_constraint\": \".isNonNegative(\\\"star_rating\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(year,None))\",\n",
      "      \"column_name\": \"year\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'year' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"year\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('year' has no negative values,year >= 0,None))\",\n",
      "      \"column_name\": \"year\",\n",
      "      \"current_value\": \"Minimum: 1999.0\",\n",
      "      \"description\": \"'year' has no negative values\",\n",
      "      \"suggesting_rule\": \"NonNegativeNumbersRule()\",\n",
      "      \"rule_description\": \"If we see only non-negative numbers in a column, we suggest a corresponding constraint\",\n",
      "      \"code_for_constraint\": \".isNonNegative(\\\"year\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(product_title,None))\",\n",
      "      \"column_name\": \"product_title\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'product_title' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"product_title\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(review_headline,None))\",\n",
      "      \"column_name\": \"review_headline\",\n",
      "      \"current_value\": \"Completeness: 0.9999987183340393\",\n",
      "      \"description\": \"'review_headline' has less than 1% missing values\",\n",
      "      \"suggesting_rule\": \"RetainCompletenessRule()\",\n",
      "      \"rule_description\": \"If a column is incomplete in the sample, we model its completeness as a binomial variable, estimate a confidence interval and use this to define a lower bound for the completeness\",\n",
      "      \"code_for_constraint\": \".hasCompleteness(\\\"review_headline\\\", lambda x: x >= 0.99, \\\"It should be above 0.99!\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(product_id,None))\",\n",
      "      \"column_name\": \"product_id\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'product_id' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"product_id\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(total_votes,None))\",\n",
      "      \"column_name\": \"total_votes\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'total_votes' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"total_votes\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('total_votes' has no negative values,total_votes >= 0,None))\",\n",
      "      \"column_name\": \"total_votes\",\n",
      "      \"current_value\": \"Minimum: 0.0\",\n",
      "      \"description\": \"'total_votes' has no negative values\",\n",
      "      \"suggesting_rule\": \"NonNegativeNumbersRule()\",\n",
      "      \"rule_description\": \"If we see only non-negative numbers in a column, we suggest a corresponding constraint\",\n",
      "      \"code_for_constraint\": \".isNonNegative(\\\"total_votes\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(product_parent,None))\",\n",
      "      \"column_name\": \"product_parent\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'product_parent' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"product_parent\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('product_parent' has no negative values,product_parent >= 0,None))\",\n",
      "      \"column_name\": \"product_parent\",\n",
      "      \"current_value\": \"Minimum: 6478.0\",\n",
      "      \"description\": \"'product_parent' has no negative values\",\n",
      "      \"suggesting_rule\": \"NonNegativeNumbersRule()\",\n",
      "      \"rule_description\": \"If we see only non-negative numbers in a column, we suggest a corresponding constraint\",\n",
      "      \"code_for_constraint\": \".isNonNegative(\\\"product_parent\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"AnalysisBasedConstraint(DataType(product_parent,None),<function1>,Some(<function1>),None)\",\n",
      "      \"column_name\": \"product_parent\",\n",
      "      \"current_value\": \"DataType: Integral\",\n",
      "      \"description\": \"'product_parent' has type Integral\",\n",
      "      \"suggesting_rule\": \"RetainTypeRule()\",\n",
      "      \"rule_description\": \"If we detect a non-string type, we suggest a type constraint\",\n",
      "      \"code_for_constraint\": \".hasDataType(\\\"product_parent\\\", ConstrainableDataTypes.Integral)\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(review_body,None))\",\n",
      "      \"column_name\": \"review_body\",\n",
      "      \"current_value\": \"Completeness: 0.9999724441818453\",\n",
      "      \"description\": \"'review_body' has less than 1% missing values\",\n",
      "      \"suggesting_rule\": \"RetainCompletenessRule()\",\n",
      "      \"rule_description\": \"If a column is incomplete in the sample, we model its completeness as a binomial variable, estimate a confidence interval and use this to define a lower bound for the completeness\",\n",
      "      \"code_for_constraint\": \".hasCompleteness(\\\"review_body\\\", lambda x: x >= 0.99, \\\"It should be above 0.99!\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('vine' has value range 'N', 'Y',`vine` IN ('N', 'Y'),None))\",\n",
      "      \"column_name\": \"vine\",\n",
      "      \"current_value\": \"Compliance: 1\",\n",
      "      \"description\": \"'vine' has value range 'N', 'Y'\",\n",
      "      \"suggesting_rule\": \"CategoricalRangeRule()\",\n",
      "      \"rule_description\": \"If we see a categorical range for a column, we suggest an IS IN (...) constraint\",\n",
      "      \"code_for_constraint\": \".isContainedIn(\\\"vine\\\", [\\\"N\\\", \\\"Y\\\"])\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(vine,None))\",\n",
      "      \"column_name\": \"vine\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'vine' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"vine\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('vine' has value range 'N' for at least 99.0% of values,`vine` IN ('N'),None))\",\n",
      "      \"column_name\": \"vine\",\n",
      "      \"current_value\": \"Compliance: 0.9939271462617969\",\n",
      "      \"description\": \"'vine' has value range 'N' for at least 99.0% of values\",\n",
      "      \"suggesting_rule\": \"FractionalCategoricalRangeRule(0.9)\",\n",
      "      \"rule_description\": \"If we see a categorical range for most values in a column, we suggest an IS IN (...) constraint that should hold for most values\",\n",
      "      \"code_for_constraint\": \".isContainedIn(\\\"vine\\\", [\\\"N\\\"], lambda x: x >= 0.99, \\\"It should be above 0.99!\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('marketplace' has value range 'US', 'UK', 'DE', 'JP', 'FR',`marketplace` IN ('US', 'UK', 'DE', 'JP', 'FR'),None))\",\n",
      "      \"column_name\": \"marketplace\",\n",
      "      \"current_value\": \"Compliance: 1\",\n",
      "      \"description\": \"'marketplace' has value range 'US', 'UK', 'DE', 'JP', 'FR'\",\n",
      "      \"suggesting_rule\": \"CategoricalRangeRule()\",\n",
      "      \"rule_description\": \"If we see a categorical range for a column, we suggest an IS IN (...) constraint\",\n",
      "      \"code_for_constraint\": \".isContainedIn(\\\"marketplace\\\", [\\\"US\\\", \\\"UK\\\", \\\"DE\\\", \\\"JP\\\", \\\"FR\\\"])\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(marketplace,None))\",\n",
      "      \"column_name\": \"marketplace\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'marketplace' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"marketplace\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('marketplace' has value range 'US' for at least 99.0% of values,`marketplace` IN ('US'),None))\",\n",
      "      \"column_name\": \"marketplace\",\n",
      "      \"current_value\": \"Compliance: 0.9949982985884372\",\n",
      "      \"description\": \"'marketplace' has value range 'US' for at least 99.0% of values\",\n",
      "      \"suggesting_rule\": \"FractionalCategoricalRangeRule(0.9)\",\n",
      "      \"rule_description\": \"If we see a categorical range for most values in a column, we suggest an IS IN (...) constraint that should hold for most values\",\n",
      "      \"code_for_constraint\": \".isContainedIn(\\\"marketplace\\\", [\\\"US\\\"], lambda x: x >= 0.99, \\\"It should be above 0.99!\\\")\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"ComplianceConstraint(Compliance('verified_purchase' has value range 'Y', 'N',`verified_purchase` IN ('Y', 'N'),None))\",\n",
      "      \"column_name\": \"verified_purchase\",\n",
      "      \"current_value\": \"Compliance: 1\",\n",
      "      \"description\": \"'verified_purchase' has value range 'Y', 'N'\",\n",
      "      \"suggesting_rule\": \"CategoricalRangeRule()\",\n",
      "      \"rule_description\": \"If we see a categorical range for a column, we suggest an IS IN (...) constraint\",\n",
      "      \"code_for_constraint\": \".isContainedIn(\\\"verified_purchase\\\", [\\\"Y\\\", \\\"N\\\"])\"\n",
      "    },\n",
      "    {\n",
      "      \"constraint_name\": \"CompletenessConstraint(Completeness(verified_purchase,None))\",\n",
      "      \"column_name\": \"verified_purchase\",\n",
      "      \"current_value\": \"Completeness: 1.0\",\n",
      "      \"description\": \"'verified_purchase' is not null\",\n",
      "      \"suggesting_rule\": \"CompleteIfCompleteRule()\",\n",
      "      \"rule_description\": \"If a column is complete in the sample, we suggest a NOT NULL constraint\",\n",
      "      \"code_for_constraint\": \".isComplete(\\\"verified_purchase\\\")\"\n",
      "    }\n",
      "  ]\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "from pydeequ.suggestions import *\n",
    "\n",
    "suggestionResult = ConstraintSuggestionRunner(spark) \\\n",
    "             .onData(df) \\\n",
    "             .addConstraintRule(DEFAULT()) \\\n",
    "             .run()\n",
    "\n",
    "print(json.dumps(suggestionResult, indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For more info ... look at full list of suggestions in `docs/suggestions.md` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}